How to run the following sql maximum efficiency. Various tips can be, for example index, sql wording and so on.

Category: MS-SQL Server -> Difficult Problems Author: shuaiyangyang Date: 2001-11-30 17:30:59
 
shuaiyangyang
2001-11-30 17:30:59

update pttx set batchid = @ batchid from pttx inner join propertyfees pf on pttx.pt = pf.id and pttx.batchid = 0 ; and pf.duedate <= @ sd2 inner join shoppinglineitem sh on pf.shoppinglineitemid = sh.id inner join lease l on sh.leaseid = l.leaseid and l . type> 0 and l.leasenum> 0 inner join # tmp p on l.addressid = p.accountid where (p.ownerid = sh.chargeto and pf.inout = 0) or (p.ownerid = sh.venderid and pf.inout = 1) and abs (isnull (sh.trustaccountid, 1)) = case when @ trustaccountid = 0 then ; abs (isnull (sh.trustaccountid, 1)) else @ trustaccountid end and ((isnull (sh.voidshow, 0) = 0 and sh.subtotal> = 0) or (isnull (sh.voidshow, 0) = 1 and sh.subtotal> 0))
a30180248
2001-11-30 17:42:49
1. or you want to get the logical relationship clear, I hope logic no problem.
2. ability to try to filter a statement on the front. For example, a table needs to be filtered can be filtered again Join.

Update pttx set batchid = @ batchid
from pttx
inner join propertyfees pf on pttx.pt = pf.id and pttx.batchid = 0 and pf.duedate <= @ sd2
inner join
(
select ; * from shoppinglineitem sh
where abs (isnull (sh.trustaccountid, 1)) =
case when @ trustaccountid = 0 then abs (isnull (sh. trustaccountid, 1)) else @ trustaccountid end
and ((isnull (sh.voidshow, 0) = 0 and sh.subtotal> = 0) or ; (isnull (sh.voidshow, 0) = 1 and sh.subtotal> 0))
)
sh on pf.shoppinglineitemid = sh.id
inner ; join lease l on sh.leaseid = l.leaseid and l.type> 0 and l.leasenum> 0
inner join # tmp p on l. addressid = p.accountid
where (p.ownerid = sh.chargeto and pf.inout = 0) or (p.ownerid = sh.venderid and pf.inout = 1)
huangjiarun
2001-11-30 17:52:21
see table scan execution plan is being consumed
tongyhuang
2001-11-30 18:06:34
very sorry everyone, these days did not come to look over.

dutguoyi ( Fresh fish row) program a bit unrealistic to me, because this is my ten million data A table can not build many indexes.
(
select * from shoppinglineitem sh
where abs (isnull (sh.trustaccountid, 1)) =
case when @ trustaccountid = 0 then abs (isnull (sh.trustaccountid, 1)) ; else @ trustaccountid end
and ((isnull (sh.voidshow, 0) = 0 and sh.subtotal> = 0) or (isnull (sh.voidshow, 0) = 1 and sh.subtotal> 0))
) filter conditions here are no indexes. ten million data full table scan is wrong.