SQL Server Performance Forum – Threads Archive
Same SP, different plans
I’m really stumped as to why this would happen at all. I have a web site that makes a call to a stored procedure. When the call is made from the web site, the following plan is generated (taken from SQL Profiler):<br /><pre>exec s_Inventory NULL, NULL, ”, ”, ”, ”, ”, ”, ”, ‘Pawn Receipt’, ‘37992’, ’16’, NULL, NULL<br /><br />Execution Tree<br />————–<br />Hash Match(Flow Distinct, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Union1016], [Union1017], [Union1018], [Union1019], [Union1020], [Union1021], [Union1022], [Union1023], [Union1024], [Union1025], [Union1026], [Union1027], [Union1028], [Union1029], [Union1030], [Union1031], [Union1032]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />((((((((((((((([Union1016]=[Union1016] AND [Union1017]=[Union1017]) AND [Union1018]=[Union1018]) AND [Union1019]=[Union1019]) AND [Union1020]=[Union1020]) AND [Union1021]=[Union1021]) AND [Union1022]=[Union1022]) AND [Union1023]=[Union1023]) AND [Union1024]=[Union1024]) AND [Union1025]=[Union1025]) AND [Union1026]=[Union1026]) AND [Union1027]=[Union1027]) AND [Union1028]=[Union1028]) AND [Union1029]=[Union1029]) AND [Union1030]=[Union1030]) AND [Union1031]=[Union1031]) AND [Union1032]=[Union1032]))<br /> |–Concatenation<br /> |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[TransID]) WITH PREFETCH)<br /> | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1000]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Customer] AS [C]))<br /> | | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[CustID]) WITH PREFETCH)<br /> | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[TransID], [P].[PropertyID]) WITH PREFETCH)<br /> | | | |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([P].[LocationOrgID]=NULL AND [T].[OrgID]=16) OR [P].[LocationOrgID]=16))<br /> | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Transactions].[IX_Transactions_OrgIDCustID] AS [T]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[OrgTransID]=37992))<br /> | | | | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Property].[IX_PropertyStatus] AS [P]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[LocationOrgID]=NULL OR [P].[LocationOrgID]=16))<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[TransItemsXRF].[IX_TransItemsXRF_Property] AS [Tx]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Tx].[TransID]=[T].[TransID] AND [Tx].[PropertyID]=[P].[PropertyID]) ORDERED FORWARD)<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Customer].[PK_Customer] AS [C]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[C].[CustId]=[T].[CustID]) ORDERED FORWARD)<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Purchases].[PK_Purchase]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Purchases].[TransID]=[T].[TransID]) ORDERED FORWARD)<br /> |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[CustID]) WITH PREFETCH)<br /> |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[OrgTransID]=37992))<br /> | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1010]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Transactions] AS [T]))<br /> | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TR].[TransID]) WITH PREFETCH)<br /> | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><i>.[PropertyID]) WITH PREFETCH)<br /> | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><I>.[PropertyID]) WITH PREFETCH)<br /> | | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Inventory] AS <I>), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><I>.[OrgID]=16))<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Property].[IX_Property_InventorySearch] AS [P]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[PropertyID]=<I>.[PropertyID]) ORDERED FORWARD)<br />
| | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[TransItemsXRF].[PK_TransItemsXRF] AS [TR]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TR].[PropertyID]=<I>.[PropertyID]) ORDERED FORWARD)<br /> | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Transactions].[IX_Transactions_TimeStamp] AS [T]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[TransID]=[TR].[TransID]) ORDERED FORWARD)<br /> |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Customer].[IX_Customer_CustIDLastName] AS [C]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[C].[CustId]=[T].[CustID]) ORDERED FORWARD)<br /></pre><br /><br />But when I execute the <i>exact same</i> SP call from Query Analyzer, I get a different plan:<br /><br /><pre>Execution Tree<br />————–<br />Merge Join(Union)<br /> |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[TransID]) WITH PREFETCH)<br /> | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[C].[LastName] ASC, [C].[FirstName] ASC, [C].[CustId] ASC, [T].[TimeStamp] ASC, [Tx].[PropertyID] ASC, [T].[TransID] ASC))<br /> | | |–Merge Join(Left Outer Join, MANY-TO-MANY MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[CustID])=([C].[CustId]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[C].[CustId]=[T].[CustID]))<br /> | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />(([P].[LocationOrgID]=NULL AND [T].[OrgID]=16) OR [P].[LocationOrgID]=16) AND ([P].[LocationOrgID]=NULL OR [P].[LocationOrgID]=16)))<br /> | | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1003]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Property] AS [P]))<br /> | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Tx].[PropertyID]) WITH PREFETCH)<br /> | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[TransID]))<br /> | | | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[CustID] ASC))<br /> | | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Transactions].[IX_Transactions_OrgIDCustID] AS [T]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[OrgTransID]=37992))<br /> | | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[TransItemsXRF].[IX_TransItemsXRF_Property] AS [Tx]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Tx].[TransID]=[T].[TransID]) ORDERED FORWARD)<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Property].[PK_Property] AS [P]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[PropertyID]=[Tx].[PropertyID]) ORDERED FORWARD)<br /> | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Customer].[IX_Customer_CustIDLastName] AS [C]), ORDERED FORWARD)<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Purchases].[PK_Purchase]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Purchases].[TransID]=[T].[TransID]) ORDERED FORWARD)<br /> |–Sort(DISTINCT ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[C].[LastName] ASC, [C].[FirstName] ASC, [C].[CustId] ASC, [T].[TimeStamp] ASC, [P].[PropertyID] ASC, [P].[Description] ASC, [T].[TransID] ASC, [P].[Stock] ASC, [P].[Status] ASC, [P].[Price] ASC, [TR].[Amount] ASC, [T].[TransType] ASC, [T].[VoidDate] ASC))<br /> |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[CustID]) WITH PREFETCH)<br /> |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[OrgTransID]=37992))<br /> | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1010]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Transactions] AS [T]))<br /> | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TR].[TransID]) WITH PREFETCH)<br /> | |–Nested Loops(
Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><I>.[PropertyID]) WITH PREFETCH)<br /> | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><I>.[PropertyID]) WITH PREFETCH)<br /> | | | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Inventory] AS <I>), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><I>.[OrgID]=16))<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Property].[IX_Property_InventorySearch] AS [P]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[PropertyID]=<I>.[PropertyID]) ORDERED FORWARD)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[TransItemsXRF].[PK_TransItemsXRF] AS [TR]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TR].[PropertyID]=<I>.[PropertyID]) ORDERED FORWARD)<br /> | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Transactions].[IX_Transactions_TimeStamp] AS [T]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[T].[TransID]=[TR].[TransID]) ORDERED FORWARD)<br /> |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[BWIdent].[dbo].[Customer].[IX_Customer_CustIDLastName] AS [C]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[C].[CustId]=[T].[CustID]) ORDERED FORWARD)<br /></pre><br /><br />What gives? Why would SQL Server do that? As you might notice in the second execution plan, there is a UNION in the SQL. The stored procedure actually builds a string based on parameters passed which then gets executed using EXECUTE(). The first call times out on the web site (takes more then 60 seconds) but when I call the stored procedure from Query Analyzer, it takes about 4 seconds.<br /><br />Any help would be appreciated.<br /><br />Francois
there might be some difference in the way NULL values are passed in
you might want to print the string before executing it to see if there are any differences
i am thinking that one cause of the poor performance is the hash join in the first plan,
if a hash join involves many row (>10,000) in certain circumstances, SQL will spool results into a temp table even if the server has plenty of memory
if the temp db is not setup for high thru-put, high row count hash joins will perform poorly
I agree with joechang that the way the UNION is being run is causing the problem. Also, his idea about printing the string first is a very good idea to help diagnose this problem. While this may not be what is causing the problem in your case, I have seen cases where I have the same exact query use a HASH one time, and a MERGE the other, for no apparent reason. In some cases I traced it back to statistics not be updated often enough, and when the statistics were updated, things worked normally. In other cases, I was not able to find the cause of the problem, so I used a UNION hint to force the type of operation I wanted, and this solved the problem. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
In our experince (with java calling s/p’s) they have a tendency to not handle nulls well (or at all). You seem to have a massive sql statement in there, can you post what it looks like? Maybe you can tune it for better efficiency. Brett
I have come to the same conclusion as bradmcgehee and have used a union hint (OPTION (MERGE UNION)) and that fixed the problem. The query itself is dynamically generated by the stored procedure based on options choosen by the user. The resulting query is quite large. Here it is: — Dynamic SQL
SELECT Distinct C.LastName, C.FirstName, C.CustID, T.TimeStamp, P.PropertyID,
P.Description, T.TransID, P.Stock, P.Status, P.Price, Tx.Amount,
T.TransType, T.VoidDate, T.OrgID, T.OrgTransID, Purchases.PawnTicket,
null AS Cost
FROM Customer C RIGHT OUTER JOIN Transactions T ON C.CustId = T.CustID
INNER JOIN TransItemsXRF Tx ON T.TransID = Tx.TransID
INNER JOIN Property P ON Tx.PropertyID = P.PropertyID
LEFT OUTER JOIN Purchases ON Purchases.TransID = T.TransID
WHERE ((P.LocationOrgID IS NULL AND T.OrgID = 16) OR P.LocationOrgID = 16)
AND T.OrgTransID = 37992 UNION SELECT Distinct C.LastName, C.FirstName, C.CustID, T.TimeStamp, P.PropertyID,
P.Description, T.TransID, P.Stock, P.Status, P.Price, TR.Amount,
T.TransType, T.VoidDate, I.OrgID, T.OrgTransID, null, null
FROM Property P INNER JOIN Inventory I ON P.PropertyID = I.PropertyID
LEFT OUTER JOIN TransItemsXRF TR ON P.PropertyID = TR.PropertyID
LEFT OUTER JOIN Transactions T ON TR.TransID = T.TransID
LEFT OUTER JOIN Customer C ON T.CustID = C.CustID
LEFT OUTER JOIN Purchases ON T.TransID = Purchases.TransID
WHERE I.OrgID = 16 And T.OrgTransID = 37992
]]>