October 29, 2008 at 9:50 am
Hi, I happen to have a problem with a huge set of queries, procedures and tables. The lot works perfectly in Developper Edition version, but I had some problem with the Standard Edition, in production.
Both databases are exactly the same, since I restored the Prod version (Standard) on my development machine (Developper Edition). And the query that never ends on the Prod, worked perfectly on my machine.
So I read on the indexed views, and they say the query hints "with (noExpand)" sometimes creates problem on versions that are not Enterprise or Developper Editions. This is when i decided to restore the same database on a Express Edition version, to see If i can reproduce the problem. And yet, no the problem is not happening on the Express Edition.
Is there anything I am missing there? I mean, the query works on Developper AND Express editions, but not on Standard.
All the machines are pretty much the same on the hardware side, even though the Prod has a dual-Core and more memory. (It should only run faster, so that is not an issue).
Anyone has a hint for me?
I cannot really post any data or queries here, because it needs a load of procedures and massive amounts of data. All I can say is that when I join 2 indexed views together using the "with (Noexpand)" hint on both views, the query never ends.
Thanks in advance,
Cheers,
J-F
October 29, 2008 at 10:05 am
Any differences in execution plans between dev and prod?
October 29, 2008 at 10:21 am
The caveat I usually have with the NOEXPAND hint is about the same as I would with most hints like that: sometimes they hurt more than they help. In the same way as forcing a query to use a specific kind of join or a specific index, you may be locking yourself out of a more efficient exec plan.
Is the indexing on the view actually truly helping this specific query's execution, or only somewhat? Remember that NOEXPAND essentially forces the view to be materialized (in its entirety). If the indexing isn't covering, this would essentially create the equivalent of bookmark lookups against the materialized view (which we all know is VERY expensive).
If you take out the NOEXPAND - do you find that the exec plan uses the indexed view, or does it go back to using it as a "regular" view (inlining the text and creating an exec plan once the text of the view has been replaced into the outer query)?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2008 at 11:18 am
Yes the query plan differs, but only a bit. I can post both here, but I don't think it would help. The only thing that differs is an "index spool", that adds in on the machines that work.
For the with (noexpand), I have tried several combinations for the past months, and couldn't find a solution to work on both editions. (Somewhen i added this hint, and the Prod worked, but some other day, the query did not work. (not the same load of data, or different validations at that time). It's been a few weeks there was no complain on the performance of the queries, so I thought I had the solution, but no, it still does not work.
My question is, what is the difference between the Standard and Express Edition for that issue, Express works, and Standard do not. I would've expected the express not to work, or both not working would've been fine too. I think this is a standard edition Issue, but I cannot understand why. I am starting to be familiar with query plans, even though I don't know everything, so I thought that hint would help me.
Here, I'll post the query plans.
Cheers,
J-F
October 29, 2008 at 11:23 am
Maybe the query itself would help, even though I cannot give the tables or some data for them.
Here is the query:
UPDATE vwGICSPFVendorUpdateAndKey
SET MfrCode = sub.MfrCode
FROM (SELECT DISTINCT u.VendorCode,
u.Updatekey,
--p.VendorCode,
p.MfrCode
--p.UpdateKey
FROM vwGICSPFVendorUpdateAndKey u WITH (noexpand)
INNER JOIN vwGICSPFProductAndKey p WITH (noexpand)
ON u.VendorCode = p.VendorCode
AND u.UpdateKey = p.UpdateKey
WHERE u.MfrCode IS NULL
AND (u.TransactionCode <> 'N'
OR u.TransactionCode IS NULL)) AS sub
WHERE vwGICSPFVendorUpdateAndKey.VendorCode = sub.VendorCode
AND vwGICSPFVendorUpdateAndKey.UpdateKey = sub.UpdateKey
Maybe rewriting the query differently could help?
Cheers,
J-F
October 29, 2008 at 11:42 am
I think you issue is that you're not specifically mentioning the view you're updating in the FROM clause. That kind of stuff tends to be a timebomb of sorts, where it will work okay at times, and blow up/go off to sleep at other times.
You might care to explicitly make sure you add the view (or preferrably the base table you're updating) in the FROM, use ANSI joins to it, and then make sure the UPDATE reflects which table you're updating in this way. I don't even know exactly what the pattern is, but the syntax you're showing has caused no end of issues on some systems of mine.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2008 at 12:14 pm
If I understand what you mean correctly, you are saying it would be quicker to do something like this:
UPDATE vwGICSPFVendorUpdateAndKey
SET MfrCode = u.MfrCode
FROM ( SELECT u.GICSPFPricingVerifyID,
sub.MfrCode
FROM vwGICSPFVendorUpdateAndKey u
INNER JOIN ( SELECT DISTINCT
u.VendorCode,
u.Updatekey,
p.MfrCode,
u.GICSPFpricingVerifyID
FROM vwGICSPFVendorUpdateAndKey u WITH ( NOEXPAND )
INNER JOIN vwGICSPFProductAndKey p
WITH ( NOEXPAND ) ON u.VendorCode = p.VendorCode
AND u.UpdateKey = p.UpdateKey
WHERE u.MfrCode IS NULL
AND ( u.TransactionCode <> 'N'
OR u.TransactionCode IS NULL
)
) AS sub ON u.GICSPFPricingVerifyID = sub.GICSPFpricingVerifyID
) u
WHERE vwGICSPFVendorUpdateAndKey.GICSPFpricingVerifyID = u.GICSPFpricingVerifyID
Otherwise, maybe updating the table behind the view could optimize the query itself?
Cheers,
J-F
October 29, 2008 at 1:58 pm
Not quite - what I've seen that acts oddly on occasion is when you have
UPDATE blah
set ....
FROM
anotherBlah
where blah.id=anotherblah.id
Instead of
UPDATE blah
set ....
FROM
blah
INNER JOIN anotherBlah ON blah.id=anotherblah.id
Especially when using the same objects in a sub-query as you do in the outer query and there may be some issue with knowing which scope it's dealing with.
Finally - since you only can be updating a single table during an UPDATE, I'd advocate never using a view name as the base object in an update. (nor can a view being updated be set to NOEXPAND. It's going to HAVE to expand to the original table, or else it's got nothing to update).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2008 at 2:18 pm
The real difference I see would be the number of CPU's. That slight difference in the execution plan could be the live system selecting a parallel plan.
Just to eliminate this as a possibility - set the MAXDOP option to 1 to force a single processor to be used.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply