I have written one sql which is having performance problem.
This table is partitioned on EVENTDATE column and having 10 crs records in it.
The block of qry which slow down I am posting here. In short what I did in that block is
I have one temporary table where I collected sub result so that I can use it for further data fetch.
I am possessing 15000 records from that temporary table each time as this help me in performance with compare to situation when I processed all temp table in one shot
when this temporary table having 100000 records performance is ~6 mins that hv to reduce.
declare @ROWPROCESSED int
SELECT @ROWPROCESSED = 0
while (@ROWPROCESSED <= (select MAX(ROWNO) from #temp))
insert into #temp2(QUANSTOCKNKG, QUANCONDNKG, EVENTDATE)
select T1.QUANSTOCKNKG as QUANSTOCKNKG , T1.QUANCONDNKG as QUANCONDNKG, T1.EVENTDATE as EVENTDATE from(
SELECT O.QUANSTOCKNKG as QUANSTOCKNKG , O.QUANCONDNKG as QUANCONDNKG, O.EVENTDATE as EVENTDATE
from ORDERSTOCKFACTS O --#temp T
--join ORDERSTOCKFACTS O on O.MILLORDNO = T.MILLORDNO and O.MACHCHAINNO = T.MACHCHAINNO and O.ORDERLINENO = T.ORDERLINENO
exists(select 1 from #temp T where O.MILLORDNO = T.MILLORDNO and O.MACHCHAINNO = T.MACHCHAINNO and O.ORDERLINENO = T.ORDERLINENO
and T.ROWNO > @ROWPROCESSED and T.ROWNO < @ROWPROCESSED + 15001)
and O.EVENTDATE >= convert(smalldatetime,'01-12-2011',105) and O.EVENTDATE <= convert(smalldatetime,'01-5-2015',105)
and O.QUALITYCODE = 'P' AND O.INVSTATUS = 'UN' AND O.READINESS = 'F'
--and T.ROWNO > @ROWPROCESSED and T.ROWNO < @ROWPROCESSED + 15001
select @ROWPROCESSED = @ROWPROCESSED + 15000
The middle block is the query which is actually time consuming. Can anybody suggest what can I do to get ride on this performance issue.
table definition and index I have attached. find attachment for execution plan also...
Please suggest if you feel what we could have to improve the performance