|
|
Posted by elite150deluxe on 08/29/06 18:36
Thank you for your response...here is a little more information:
1. there are about 170,000 records in the temptable/derived table
2. i accidentally included the "category" criteria...it does affect
the performance either way...sorry, i didn't want to confuse the
issue...
3. The main source table "licenses" contains about 10,000 records
4. When i look at the Execution Plan, it seems to be taking a long
time on the clustered index of the "identities" table. Which seems
weird that is it slowing down dealing with one of the indexes for the
derived table. I wasn't expecting this.
You may be right about the optimizer not assessing this query
correctly. I do not know much about how the optimizer makes
determinations about execution of the query. My hope is that is would
do the following:
1. Retrieve all the rows from the "licenses" table
2. For each row in the "licenses" table, join out to the result
of the derived table using the WHERE clause to restrict the number of
rows in each correlated query.
3. Return the TOP 1 of that correlated query.
here is the "bad" query again (simplified):
****************************************************************************
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
ORDER BY mi.created DESC
) as messageid
FROM licenses T1
****************************************************************************
- Steve
Alexander Kuznetsov wrote:
> it might be that the optimizer underestimates the cardinality of the
> subquery:
>
> 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
>
> and comes up with a plan more appropriate for a small number of orws.
> How many rows are there in the temporary table?
> Are licenseKey AND category columns in one and the same table?
> If yes, you could try to create a computed column named
> licenseKey_category AS CAST(licenseKey AS VARCHAR(...)) + ' ' +
> category,
> create an index on it, and rewrite your subquery instead of
>
> WHERE l.licenseKey = t1.licenseKey AND category = 'usage'
>
> try to use
>
> WHERE l1.licenseKey_category = CAST(licenseKey AS
> VARCHAR(...)) + ' usage'
>
> That's just one possibility.
Navigation:
[Reply to this message]
|