Pivot multiple input parameter in SQL server?How to reduce query running time -
below query have writing daily inventory-sale each shop in have 2millon row data month have more 250 shop!! take longer expected !!
alter procedure [dbo].[getdaily] --declare @date1 date = '2015-06-01' ,@date2 date = '2015-06-30' ,@storeno nvarchar(max)=' , s.storeno in (61,63,450,451)' begin if object_id(n'tempdb..#calender') not null drop table #calender if object_id(n'tempdb..##temp') not null drop table ##temp if object_id(n'tempdb..##inv1') not null drop table ##inv1 if object_id(n'tempdb..##inv2') not null drop table ##inv2 if object_id(n'tempdb..#stock') not null drop table #stock if object_id(n'tempdb..##storelist') not null drop table ##storelist if object_id(n'tempdb..##final') not null drop table ##final if object_id(n'tempdb..##product') not null drop table ##product declare @sql_store nvarchar(max) set @sql_store = 'select storeno,storename ##storelist store s storeno in (61,63,450,451)' + @storeno execute sp_executesql @sql_store declare @date_s char(10) = cast(@date1 varchar) ,@date_e char(10) = cast(@date2 varchar) ,@date_index date create table #calender (date date) set @date_index = @date1 while @date_index<=@date2 begin insert #calender select @date_index set @date_index = dateadd(day,1,@date_index) if @date_index>@date2 break else continue end begin select productno productbarcode,date ##product product,#calender startusedate <= @date_s , endusedate >=@date_e , isinused = 1 end create table #inventory (storeno int ,date date, productbarcode varchar(14),productqty int ) begin select std.storeno storeno ,checkdate date ,productbarcode productbarcode , sum( stocktakingqty)as productqty ##temp stocktakingdetail std inner join (select storeno ,checkno ,checkdate stocktakingmain sm )stocktakingmain on std.checkno =stocktakingmain.checkno group std.storeno,std.productbarcode,checkdate --insert #inventory select a.storeno ,c.[date],a.productbarcode,a.productqty ##inv1 #calender c outer apply ( select top 50 percent * ##temp i.date < c.date , storeno in (select storeno ##storelist ) order i.date ) option (maxrecursion 0) --insert #inventory select b.storeno ,c.[date],b.productbarcode,b.productqty ##inv2 #calender c outer apply ( select top 50 percent * ##temp i.date > c.date , storeno in (select storeno ##storelist ) order i.date ) b option (maxrecursion 0) insert #inventory select s.storeno,s.date,s.productbarcode,isnull (sum(s.productqty),0) productqty (select storeno,date,productbarcode,productqty ##inv1 union select storeno,date,productbarcode,productqty ##inv2 )s storeno not null group s.storeno,s.date,s.productbarcode end begin select s.date date,s.storeno storeno,(s.productbarcode ) productbarcode,sum(s.qty) productqty #stock --- sale ( select storeno storeno, plucode productbarcode, cast (sum(buypoint/100)as int) qty, date date pos_itemtran p date between @date_s , @date_e , p.storeno in(select storeno ##storelist) group plucode,storeno,date union --transfer in select td.targetstoreno storeno ,td.productbarcode productbarcode ,sum(td.acceptqty)*(-1) qty ,acceptdate date transferdetail td inner join (select transferserialno ,acceptdate transfermain )tm on tm.transferserialno = td.transferserialno acceptdate between @date_s , @date_e , targetstoreno in (select storeno ##storelist ) group targetstoreno,td.productbarcode,acceptdate union -- transfer out select td.transferstoreno storeno , td.productbarcode productbarcode ,sum(td.transferqty) qty ,transferdate date transferdetail td inner join (select transferserialno ,transferdate transfermain)tmo on tmo.transferserialno = td.transferserialno transferdate between @date_s , @date_e , transferstoreno in (select storeno ##storelist ) group td.transferstoreno,td.productbarcode,transferdate --loss union select storeno ,lossproductbarcode ,sum(lossqty) qty, lossdate date lossdetail ld inner join (select storeno ,lossserialno ,lossdate lossmain lm)lossmain on ld.lossserialno = lossmain.lossserialno lossdate between @date_s , @date_e , storeno in (select storeno ##storelist ) group storeno,lossproductbarcode,lossdate union --buy select buystore storeno ,productbarcode productbarcode ,sum(buyqty)*(-1)as qty ,buydate date buydetail bd inner join (select buyserialno ,buydate buymain bm) buymain on bd.buyserialno = buymain.buyserialno buydate between @date_s , @date_e , buystore in (select storeno ##storelist ) group buydate,buystore,productbarcode,buydate union --rcvbackdetail select rcvbackstoreno storeno ,productbarcode productbarcode ,sum(rcvbackqty)*(-1) qty ,rcvbackdate date rcvbackdetail rbd inner join (select rcvbackserialno ,rcvbackdate rcvbackmain)rcvbackmain on rcvbackmain.rcvbackserialno = rbd.rcvbackserialno rcvbackdate between @date_s , @date_e , rcvbackstoreno in (select storeno ##storelist ) group rcvbackdate,rcvbackstoreno,productbarcode )s group s.storeno,s.productbarcode,s.date end declare @pheader nvarchar(max),@sql_pivot nvarchar(max) begin select @pheader=isnull(@pheader,'')+'['+storename+'],' ##storelist group storeno,storename order storeno set @pheader= left(@pheader, len(@pheader) - 1) set @sql_pivot= n'select * from'+ '( select storename ,p.date date ,p.productbarcode productbarcode ,isnull(i.productqty-(select productqty runningsum #stock st st.date<=i.date , st.storeno=i.storeno , st.productbarcode = i.productbarcode ),i.productqty) productqty #inventory left join store s on s.storeno = i.storeno left join #stock st on st.storeno = i.storeno , st.productbarcode = i.productbarcode , st.date = i.date left join ##product p on i.productbarcode = p.productbarcode , i.date = p.date --where p.productbarcode in (0065103700004, -- 2017961746012) group i.storeno ,storename ,i.productbarcode ,p.productbarcode ,p.date ,i.date ,i.productqty)p pivot (sum(productqty) [storename] in ('+ @pheader+n') ) pivt order date,productbarcode' execute sp_executesql @sql_pivot set @pheader = null select @pheader=isnull(@pheader,'')+'0 ['+cast(storeno varchar)+'],' ##storelist group storeno order storeno set @pheader= left(@pheader, len(@pheader) - 1) set @pheader = ' select 0 '+''''+' '+''''+', 0 '+''''+' '+''''+','+'0 '+''''+' '+''''+', 0 '+''''+' '+''''+','+@pheader execute sp_executesql @pheader end drop table #inventory --drop table #calender if object_id(n'tempdb..#inventory') not null drop table #inventory if object_id(n'tempdb..#calender') not null drop table #calender if object_id(n'tempdb..##temp') not null drop table ##temp if object_id(n'tempdb..##inv1') not null drop table ##inv1 if object_id(n'tempdb..##inv2') not null drop table ##inv2 if object_id(n'tempdb..#stock') not null drop table #stock if object_id(n'tempdb..##storelist') not null drop table ##storelist if object_id(n'tempdb..##final') not null drop table ##final if object_id(n'tempdb..##product') not null drop table ##product end i have kind of idea why slow ,if add product table taking long time !! dont know how change , write query ! how write way reduce running time , during pivot table can able add 1 store number want add more have no idea why not getting multiple parameter input!
if can suggest or help! advance
create indexes large temp tables. after populating them via (select into)
Comments
Post a Comment