Correlated Subquery Efficiency

[Jason]

Hello All,

I have a SQL Query with multiple correlated Subqueries in it. When it
gets executed it runs rather slow due to the size of the QT table.
Does anybody have any suggestions how to alter this query to make it
run faster, or any index suggestions to assist it with.

Query is as follows:
SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,

(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUM
FROM QT INNER JOIN
JU ON QT_PROC_CODE = JU_PROC_CODE
WHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)
AND (QT_QUOTE_JOB = 0))AS [PREPCOST],

(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUM
FROM QT INNER JOIN
JU ON QT_PROC_CODE = JU_PROC_CODE
WHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)
AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],

(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUM
FROM QT INNER JOIN
JU ON QT_PROC_CODE = JU_PROC_CODE
WHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)
AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],

(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUM
FROM QT INNER JOIN
JU ON QT_PROC_CODE = JU_PROC_CODE
WHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)
AND (JU_PROC_GRP > 1) AND
((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]

FROM SH
WHERE SH_ADD_DATE = '5/FEB/2004'

thanks a lot for any help
Jason

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jason (ha******@primus.com.au) writes:

I have a SQL Query with multiple correlated Subqueries in it. When it
gets executed it runs rather slow due to the size of the QT table.
Does anybody have any suggestions how to alter this query to make it
run faster, or any index suggestions to assist it with.

Yes, it looks like derived table could be a winner here. Try the
transformation below. It may not be correct every detail, so test
carefully:

SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,
PREPCOST, MATCOST, OUTCOST, LABCOST
FROM SH
JOIN (SELECT QT_NUMBER,
SUM(CASE WHEN JU_PROC_GRP < 2
THEN QT_CHARGE
ELSE 0
END) AS PREPCOST,
SUM(CASE WHEN JU_PROC_GRP > 1 AND JU_CATEG = 1
THEN QT_CHARGE
ELSE 0
END) AS MATCOST,
SUM(CASE WHEN JU_PROC_GRP > 1 AND JU_CATEG = 3
THEN QT_CHARGE
ELSE 0
END) AS OUTCOST,
SUM(CASE WHEN JU_PROC_GRP > 1 AND JU_CATEG IN (0, 2, 4)
THEN QT_CHARGE
ELSE 0
END) AS LABPCOST
FROM QT INNER JOIN
JU ON QT_PROC_CODE = JU_PROC_CODE
WHERE QU_QUOTE_JOB = 0
GROUP BY QT_NUMBER) AS x
ON x.QT_NUMBER = ' ' + SH_NOTE_2
WHERE SH_ADD_DATE = '5/FEB/2004'


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp