May 25, 2006 at 9:30 am
Any ideas why this query is running so slow? I indexed the dbo.cs_ArticleTracking.Creator, cs_EmailForCategories.email and cs_ArticleTracking.dateCreated columns, but it still seems to be running slow.
Thanks!
James
SELECT DISTINCT dbo.cs_ArticleTracking.id, dbo.cs_ArticleTracking.taskProblem, dbo.cs_ArticleTracking.dateCreated,
(SELECT firstname + ' ' + lastname AS fullname
FROM cs_users
WHERE id = dbo.cs_ArticleTracking.Creator) AS CreatorName
FROM dbo.cs_ArticleCategories INNER JOIN
dbo.cs_ArticleTracking ON dbo.cs_ArticleCategories.parent = dbo.cs_ArticleTracking.parentcategory AND
dbo.cs_ArticleCategories.child = dbo.cs_ArticleTracking.childcategory AND
dbo.cs_ArticleCategories.child2 = dbo.cs_ArticleTracking.child2category INNER JOIN
dbo.cs_EmailForCategories ON dbo.cs_ArticleCategories.id = dbo.cs_EmailForCategories.idCategory
WHERE (dbo.cs_ArticleTracking.status = 'New') AND (dbo.cs_EmailForCategories.email = 'email@email.com') OR
(dbo.cs_ArticleTracking.status = 'New') AND (dbo.cs_ArticleTracking.creator = #Session.userid#)
ORDER BY dbo.cs_ArticleTracking.dateCreated DESC
May 25, 2006 at 9:39 am
A sub-SELECT within a SELECT will always be a performance hit. It forces the sub-SELECT to be re-evaluated for every row in the main resultset and creates cursor-like behaviour.
Put the sub-SELECT into the FROM as a derived table and join to it there.
SELECT DISTINCT dbo.cs_ArticleTracking.id, dbo.cs_ArticleTracking.taskProblem, dbo.cs_ArticleTracking.dateCreated,
dtName.CreatorName
FROM dbo.cs_ArticleCategories
INNER JOIN
dbo.cs_ArticleTracking ON dbo.cs_ArticleCategories.parent = dbo.cs_ArticleTracking.parentcategory AND
dbo.cs_ArticleCategories.child = dbo.cs_ArticleTracking.childcategory AND
dbo.cs_ArticleCategories.child2 = dbo.cs_ArticleTracking.child2category
INNER JOIN
dbo.cs_EmailForCategories ON dbo.cs_ArticleCategories.id = dbo.cs_EmailForCategories.idCategory
-- Derived table to build full name for each id
INNER JOIN
(SELECT id, firstname + ' ' + lastname AS fullname AS CreatorName
FROM cs_users
) dtName
ON dtName.id = dbo.cs_ArticleTracking.Creator
WHERE (dbo.cs_ArticleTracking.status = 'New') AND (dbo.cs_EmailForCategories.email = 'email@email.com') OR
(dbo.cs_ArticleTracking.status = 'New') AND (dbo.cs_ArticleTracking.creator = #Session.userid#)
ORDER BY dbo.cs_ArticleTracking.dateCreated DESC
May 25, 2006 at 9:46 am
And the WHERE clause could be somewhat easier to optimize with
SELECT DISTINCT dbo.cs_ArticleTracking.id,
dbo.cs_ArticleTracking.taskProblem,
dbo.cs_ArticleTracking.dateCreated,
cs_users-firstname + ' ' + cs_users.lastname AS fullname
FROM dbo.cs_ArticleCategories
INNER JOIN dbo.cs_ArticleTracking ON dbo.cs_ArticleCategories.parent = dbo.cs_ArticleTracking.parentcategory AND dbo.cs_ArticleCategories.child = dbo.cs_ArticleTracking.childcategory AND dbo.cs_ArticleCategories.child2 = dbo.cs_ArticleTracking.child2category
INNER JOIN dbo.cs_EmailForCategories ON dbo.cs_ArticleCategories.id = dbo.cs_EmailForCategories.idCategory
INNER JOIN cs_users ON cs_users.id = dbo.cs_ArticleTracking.Creator
WHERE dbo.cs_ArticleTracking.status = 'New'
AND (dbo.cs_EmailForCategories.email = 'email@email.com' OR dbo.cs_ArticleTracking.creator = #Session.userid#)
ORDER BY dbo.cs_ArticleTracking.dateCreated DESC
N 56°04'39.16"
E 12°55'05.25"
May 25, 2006 at 9:48 am
Another 'clarification' may help too...
WHERE status = 'New' AND (email = 'xxx' OR creator = 'yyy')
...of course there are indices on the parent, parentcategory, child, childcat... columns, aren't there?
a composite index on ArticleTracking(status, email, creator, datecreated) might help more than the 2 separate indices.
May 25, 2006 at 12:28 pm
Great recommendations!
The only thing that I couldn't implement is this (Error: Incorrect sytax near the keyword as):
-- Derived table to build full name for each id
INNER JOIN
(SELECT id, firstname + ' ' + lastname AS fullname AS CreatorName
FROM cs_users
) dtName
ON dtName.id = dbo.cs_ArticleTracking.Creator
Thanks!!!!!
May 25, 2006 at 12:33 pm
I've never used a composite index actually. What would be the result if I had separate indexes and then a composite index too?
Thanks!
James
May 25, 2006 at 1:16 pm
Sorry, didn't notice you'd already aliased the column as FullName:
-- Derived table to build full name for each id
INNER JOIN
(SELECT id, firstname + ' ' + lastname AS CreatorName
FROM cs_users
) dtName
ON dtName.id = dbo.cs_ArticleTracking.Creator
May 25, 2006 at 6:30 pm
SELECT DISTINCT AT.id, AT.taskProblem, AT.dateCreated,
U.firstname + ' ' + U.lastname AS CreatorName
FROM dbo.cs_ArticleTracking AT
LEFT JOIN cs_users U ON U.id = AT.Creator
INNER JOIN dbo.cs_ArticleCategories AC ON AC.parent = AT.parentcategory
AND AC.child = AT.childcategory AND AC.child2 = AT.child2category
INNER JOIN dbo.cs_EmailForCategories EC ON AC.id = EC.idCategory
AND (EC.email = 'email@email.com' OR AT.creator = #Session.userid#)
WHERE (AT.status = 'New')
ORDER BY AT.dateCreated DESC
_____________
Code for TallyGenerator
May 26, 2006 at 5:13 am
To answer the composite index question...
Essentially, if you have a simple index and a composite index, they just sit next to each other, and are maintained separately. The good thing, is that it gives the query optimizer additional tools to work with, to speed up data retrieval. The bad thing, is that it is additional indexes to update when you update or insert a row. But, the two indexes don't interact with each other directly.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply