March 9, 2006 at 2:29 pm
Could anyone give me some tips on how I might speed this query up. It takes 20sec to run and you know how users are.
Thanks for your help!
Declare @BeginningDate as datetime, @EndDate as datetime
set @EndDate = '12/31/2005'
set @BeginningDate = '01/01/2005'
select inmast.fpartno, inmast.frev , rcitem.freceiver+rcitem.fitemno as Receiver,
(Select Sum(rcinsp.fnqtyfail) from rcinsp Where rcinsp.fcreceiver+rcinsp.fcitemno = rcitem.freceiver+rcitem.fitemno) as QtyFail,
rcitem.fqtyrecv, rcitem.fucost
from inmast inner Join rcitem
on inmast.fpartno + inmast.frev = rcitem.fpartno + rcitem.fpartrev and inmast.fprodcl = 'RM'
join rcmast on rcitem.freceiver = rcmast.freceiver
Where rcmast.fdaterecv >= @BeginningDate and rcmast.fdaterecv <= @EndDate
Order by inmast.fpartno, inmast.frev, Receiver
March 9, 2006 at 2:42 pm
>>(Select Sum(rcinsp.fnqtyfail) from rcinsp Where rcinsp.fcreceiver+rcinsp.fcitemno = rcitem.freceiver+rcitem.fitemno) as QtyFail,
Get this sub-select out of the SELECT and move it into the FROM. Sub-selects within a SELECT create a cursor-like query plan that runs like molasses.
March 9, 2006 at 2:47 pm
select inmast.fpartno, inmast.frev ,
rcitem.freceiver+rcitem.fitemno as Receiver,
-- QtyFail comes from derived table, not sub-select
dt.QtyFail,
rcitem.fqtyrecv, rcitem.fucost
from inmast
inner Join rcitem
-- Any particular reason why these need to be conactenated to join ?
-- Use of expressions prevents index usage and forces slow tablescan
on inmast.fpartno + inmast.frev = rcitem.fpartno + rcitem.fpartrev and
inmast.fprodcl = 'RM'
Inner join rcmast on rcitem.freceiver = rcmast.freceiver
Inner Join
-- Join to derived table that assembles QtyFail for each key
(
Select rcinsp.fcreceiver+rcinsp.fcitemno As JoinKey
Sum(rcinsp.fnqtyfail) As QtyFail
from rcinsp
Group By rcinsp.fcreceiver + rcinsp.fcitemno
) dt
On (dt.JoinKey = rcitem.freceiver+rcitem.fitemno)
Where rcmast.fdaterecv >= @BeginningDate and rcmast.fdaterecv <= @EndDate
Order by inmast.fpartno, inmast.frev, Receiver
March 9, 2006 at 3:09 pm
Yes that was much faster, but the problem I have is that not all the rcitem.freceiver are in rcinsp. So the way you wrote the query does include the null or 0 values. Any advice? And there is no reason that they have to be conactenated it was just quicker at the time
My slow why, results
fpartno frev Receiver QtyFail fqtyrecv fucost
102-430292-1 N/C 009218001 .00000 1.00000 59.36000
114S1722-25-21 E 006687001 .00000 22.00000 6.00000
114S1722-25-21 E 007024001 .00000 33.00000 6.00000
114S1722-25-21 E 007403001 .00000 22.00000 6.00000
114S1722-25-22 E 006111001 22.00000 6.00000
114S1722-25-22 E 006687003 .00000 12.00000 6.00000
114S1722-25-22 E 007024002 .00000 22.00000 6.00000
114S1722-25-22 E 007403002 .00000 33.00000 6.00000
Your revised why, results
fpartno frev Receiver QtyFail fqtyrecv fucost
102-430292-1 N/C 009218001 .00000 1.00000 59.36000
114S1722-25-21 E 006687001 .00000 22.00000 6.00000
114S1722-25-21 E 007024001 .00000 33.00000 6.00000
114S1722-25-21 E 007403001 .00000 22.00000 6.00000
114S1722-25-22 E 006687003 .00000 12.00000 6.00000
114S1722-25-22 E 007024002 .00000 22.00000 6.00000
114S1722-25-22 E 007403002 .00000 33.00000 6.00000
March 9, 2006 at 3:16 pm
Use left join:
LEFT Join
-- Join to derived table that assembles QtyFail for each key
(
March 9, 2006 at 3:33 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply