June 19, 2008 at 5:28 am
select rtrim(A.ref1) as Barcode,
A.updatedate as LastUpdated,
B.webuser as RequestorID,
C.city as WorkOrderCity
from A left outer join D on A.Aid = D.Aid
left outer join B on D.trwoid = B.trwoid
left outer join C on C.trwoid = B.trimportedwoid
where A.trcustomerid = '1'
and (D.Did in (select max(Did) from D group by Aid)
or D.trwoid is null)
Any ideas on what is taking so long, tried update stats and reindex but nothing changed
June 19, 2008 at 5:43 am
First, your table names could be a bit more descriptive.
Try aliasing the table name in your sub-query. The optimizer may be getting confused by the D table being referred to twice.
Other than that, make sure you have the approriate indexes.
[font="Courier New"]select
rtrim(A.ref1) as Barcode,
A.updatedate as LastUpdated,
B.webuser as RequestorID,
C.city as WorkOrderCity
from
A
left outer join D on A.Aid = D.Aid
left outer join B on D.trwoid = B.trwoid
left outer join C on C.trwoid = B.trimportedwoid
where
A.trcustomerid = '1'
and (D.Did in (select max(Dx.Did) from D Dx group by Dx.Aid)
or D.trwoid is null) [/font]
June 19, 2008 at 5:51 am
Still the same, it gets stuck at the same record number everytime..
June 19, 2008 at 6:51 am
Pull up the execution plan. That will help you find out where to look.
I suspect it's this:
(D.Did in (select max(Did) from D group by Aid)
or D.trwoid is null)
Try moving the IN clause up into the where clause as either a derived table or as part of the JOIN criteria for the D table. Also, try a TOP with an ORDER BY instead of the MAX.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply