November 13, 2014 at 11:36 am
Hi all,
I am having hard time to get a query to work. Would really appreciate any help on this. It contains a correlated subquery and it used to run in two seconds in SQL Server 2008 while now in SQL Server 2012 it does not finish even after 30 min.
I compared the Execution Plans of the query in the 2008 version with the query in the 2012 version and in the lookup table of the subquery this is what I noticed:
1) the 2008 version uses a table scan and an Index Spool (Eager Spool) in the lookup table of the correlated subquery
2) the 2012 version uses an Index Scan of the table, RID Lookup (Heap) and Nested Loops (inner join)
Here is the simplified code, information is pulled from SQL tables housing data from the Microsoft Navision ERP:
SELECT
SKU.[Item No_]
,SKU.[Unit Price]
,SKU.[On Hand (Actual)]
,SKU.[On Purchase Orders]
,SKU.[On Sales Orders]
,SKU.[Qty On Kit Lines]
,c.[Promised Delivery Date]
,c.[Sales Order Count]
INTO #t
FROM
(
SELECT--Listing of items in the item history table
a.[No_] as [Item No_]
,b.[Gen_ Prod_ Posting Group] as GPPG
,a.[Web Item]
,a.[Changed Date (History)]
,a.[Inventory (Static)] as [On Hand (Actual)]
,a.[Qty_ On PO (Static)] as [On Purchase Orders]
,a.[Qty_ On SO (Static)] as [On Sales Orders]
,a.[Qty_ On Kit Lines (Static)] as [Qty On Kit Lines]
FROM [Company_A Navision].dbo.[Company_A ENTERPRISES, INC_$Item_27] a with(nolock)
left join [Company_A Navision].dbo.[Company_A ENTERPRISES, INC_$Item] b with(nolock)
on a.[No_] = b.[No_]
)SKU
LEFT JOIN
--------------------------------
-- SALES ORDERS - ADD CUSTOMER
--------------------------------
(
SELECT
b.[No_]
,a.[Sell-to Customer No_]
,cus.Name
,a.[Order Date]
,a.[Shipment Date]
,min(a.[Promised Delivery Date]) as [Promised Delivery Date]
,COUNT(DISTINCT(b.[Document No_])) AS [Sales Order Count]
,sum(b.Amount) as SO_Total_Amount
from
[Company_A Navision].dbo.[Company_A ENTERPRISES, INC_$Sales Header] a with(nolock)
left join
[Company_A Navision].dbo.[Company_A ENTERPRISES, INC_$Sales Line] b with(nolock)
ON a.[No_] = b.[Document No_]
left join
[Company_A ENTERPRISES, INC_$Customer] cus
ON cus.No_=a.[Sell-to Customer No_]
WHERE b.[Document Type] =1
GROUP BY
b.[No_]
,a.[Sell-to Customer No_]
,cus.Name
,a.[Promised Delivery Date]
,a.[Order Date]
,a.[Shipment Date]
) c
ON SKU.[Item No_] = c.[No_]
or c.[No_] in ( --The correlated subquery
select [FinishedItem] from Testsql.DBO.[Kit_BOM_Component_Cost]
WHERE [fINAL cOMPONENT] =SKU.[Item No_])
November 13, 2014 at 11:49 am
Hi and welcome to the forum. Just a quick note, in order to provide a sound advice on your query, some additional information is needed. Could you please provide the actual execution plans from both servers and preferably the full DDL for both tables and indexes. In addition, a small set of consumable test data would also be helpful.
😎
November 13, 2014 at 11:50 am
1. Ensure that the columns in your join predicates and WHERE predicates are indexed.
2. The RID Lookup (Heap) says to me that the lookup table has no Clustered Index. See if you can add one if it is missing)
3. Update the index statistics for the tables involved.
Gerald Britton, Pluralsight courses
November 13, 2014 at 12:53 pm
I added a Clustered Index to the lookup table, and the plan looked better. The Nested Loop went away and the RID Lookup (Heap) went away. What remained was a Clustered Index Scan. It still runs forever though ... .
I haven't taken a look yet at the other indexes in the join/where predicates.
Thanks to both of you for the replies.
November 13, 2014 at 12:58 pm
is [fINAL cOMPONENT] indexed in the look up table? IF not, index it. That should change the scan to a seek.
Gerald Britton, Pluralsight courses
November 13, 2014 at 2:22 pm
yes it was indexed. I even tried it by creating a clustered index based on the final component and the finished item and it still runs forever and in the execution plan I did not see the change to a seek.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply