|
|
Posted by steven.fafel on 08/29/06 16:27
I am running 2 versions of a correlated subquery. The two version
differ slightly in design but differ tremendously in performance....if
anyone can answer this, you would be awesome.
The "bad" query attempts to build a result set using a correlated
subquery. The part causing the error is that the correlated subquery
is part of a derived table (joining 3 tables). Trying to run the query
takes a long time and the more records in the source table, the
performance is exponentially worse. When i change the derived table to
a fixed table, the query runs fast.
I look at the Execution Plan in Query Analyzer and the majority of time
is taken by the Clustered Index Seek and by the Nested Loops/Inner
Join.
**********************************************************************************************************
here is the "bad" query:
**********************************************************************************************************
SELECT licenseKey, (
SELECT TOP 1 mi.id FROM messages mi
INNER JOIN identities i ON i.id=mi.identityid
INNER JOIN licenses l on i.licenseid=l.id
WHERE l.licenseKey = t1.licenseKey AND category = 'usage'
ORDER BY mi.created DESC
) as messageid
FROM licenses T1
**********************************************************************************************************
here is the "good" query
**********************************************************************************************************
SELECT licenseKey, (
SELECT TOP 1 t2.id FROM temptable T2
WHERE t2.licenseKey = t1.licenseKey
ORDER BY t2.created DESC
) as messageid
FROM licenses T1
**********************************************************************************************************
Thank you in advance
Navigation:
[Reply to this message]
|