Tough Correlated Subquery issue

Giganews Newsgroups
Subject: Tough Correlated Subquery issue
Posted by:  steven.faf…@gmail.com
Date: 29 Aug 2006

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

Replies