slow query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

slow query

I have an invoicing application running slowly. After running profiler to find queries that are hogging the server, I found 2 store procedures that are the slowest (more than 2000 milliseconds). The following is one of them. The execution plan show that 70% of the cost is in (SELECT DISTINCT PackingSlipID FROM tblPackingSlipDetails
WHERE PackingSlipDetailID NOT IN (SELECT PackingSlipDetailID FROM tblInvoiceDetails)). tblPackingSlipDetails has a non-cluster index on PackingSlipDetailID.
tblInvoiceDetails also has a non-cluster index on PackingSlipDetailID. The execution plan scans both non-cluster indexes and then performs a merge join/left anti semi join.
tblCustomers has 400 rows.
tblPackingSlipDetail has 887700 rows.
tblInvoiceDetail has 919605 rows.
tblPackingSlip has 246793 rows.
tblGenericInvoiceItems has 187 rows.
Thanks in advance for any help. CREATE PROCEDURE [spq_getAllOpenPackingSlipsCustomersOnly]
AS
SELECT DISTINCT
a.CustomerID,
a.CustomerCode,
a.CheckerCode,
a.CompanyName,
a.CreatedBy,
a.IndustryID
FROM
tblCustomers a,
tblPackingSlip b
WHERE
(
(a.CustomerID = b.CustomerID) AND
(PackingSlipID IN
(SELECT DISTINCT PackingSlipID FROM tblPackingSlipDetails
WHERE
PackingSlipDetailID NOT IN
(SELECT PackingSlipDetailID FROM tblInvoiceDetails)
UNION
SELECT DISTINCT PackingSlipID FROM tblGenericInvoiceItems
WHERE
NOT (InvoiceID > 0)
)
)
)
ORDER BY a.CustomerID
Try recompiling those 2 SPs and UPDATE STATS on involved tables. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Does this query really need a SELECT DISTINCT? Most don’t, and if you don’t need it, using a standard SELECT is much faster. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
I have UPDATE statistics (Full scan option) on all tables and recompiled the 2 store procedures. The performance afterward is the same as before. The execution plans are the same. I have tried elminating the DISTINCT. But the improvement is not noticable. Perhaps because of the small number of rows the DISTINCT needs to work on. According to the execution plan, 70% of the cost of doing the query belongs to the scanning the 2 non-cluster indexs(each has about 900,000 entries) and merge-joining the result. Is it possible to get significant improvement from tuning this query?
Make a covering index with the most selective column as your first column. See what that gives you. Just know that it will add extra writes when you update, insert, or delete from the column. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Try using an outer join instead of NOT IN: SELECT DISTINCT T1.PackingSlipID
FROM tblPackingSlipDetails T1
LEFT JOIN tblInvoiceDetails T2
ON T1.PackingSlipDetailID = T2.PackingSlipDetailID
WHERE T2.PackingSlipDetailID IS NULL … or NOT EXISTS … SELECT DISTINCT T1.PackingSlipID
FROM tblPackingSlipDetails T1
WHERE NOT EXISTS
(SELECT T2.PackingSlipDetailID FROM tblInvoiceDetails T2
WHERE T2.PackingSlipDetailID = T1.PackingSlipDetailID)

I would recommend what Adriaan has suggested. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
I have tried
SELECT DISTINCT T1.PackingSlipID
FROM tblPackingSlipDetails T1
LEFT JOIN tblInvoiceDetails T2
ON T1.PackingSlipDetailID = T2.PackingSlipDetailID
WHERE T2.PackingSlipDetailID IS NULL also i have tried
SELECT DISTINCT T1.PackingSlipID
FROM tblPackingSlipDetails T1
WHERE NOT EXISTS
(SELECT T2.PackingSlipDetailID FROM tblInvoiceDetails T2
WHERE T2.PackingSlipDetailID = T1.PackingSlipDetailID) Both schemes take about the same time to finish. Performance is the about the same as old method. I am testing to add a new table to keep track of open packing slips. AFTER INSERT and AFTER DELETE triggers are added to tblInvoiceDetails and tblPackingSlipDetails to maintain the open_packing_slips table. Using the open_packing_slips table, performance has been improved and response time was reduce 10 folds. But is it a problematic approach in the long run?
Did you try the LEFT JOIN query without DISTINCT? If there is no improvement in response time, check the execution plan to see if the index is being used at all (no table scan). I also notice that I was working on the part of the query that you quoted in your description, and that in the full procedure there’s another item in the subquery that can be bad for performance – the UNION with the NOT criteria. Put all your tables in the main query, and use JOINs: INNER JOIN between tblCustomers and tblPackingSlip, OUTER JOIN to tblPackingSlipDetails with WHERE … IS NULL. Not sure what the UNION operator is supposed to do: the query statement implies that InvoiceID and PackingSlipID are fields in tblGenericInvoiceItems – but that’s a reference table with only 187 records. Are you sure there is no error when you run the query directly in QA? Also, you’re UNIONing values for PackingSlipID and PackingSlipDetailID, which as far as I can tell are two different entities.
Also note that UNION effectively does a DISTINCT on the result set.
Either remove the DISTINCTS, or change the UNION to a UNION ALL Not sure this is relevant to your current query, but just for future reference
index index index index Have you looked at this? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>