sql server - SQL Index Spool(eager spool) Speed up Query -
i have stored procedure wrote while generate xml file used share data external resources. end user dumps data inside table called datasharing , when execute query return xml document required fields specified inside datasharing. procedure work extremely slow. when run via ssms , set 'show actual execution plan', 94% of query spent on index spool (eager spool). upon researching looks should rework query perform better.
since columns of data never know had unique pivot in order generate data.
here procedure:
create procedure [dbo].[sp_hpsddatasharing] -- add parameters stored procedure here @filename varchar(max), @startdate datetime, @enddate datetime begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; declare @sqlcommand varchar(max), @liststr varchar(max) select @liststr = coalesce(@liststr +',' ,'') + '[' + [columnname] + ']' [fcpp_hpsd].[dbo].[datasharing] filename = @filename declare @result xml set @sqlcommand = 'select * ( select [datapointdate] ,dp.columnname ,[datapointvalue] [fcpp_hpsd].[dbo].[vw_datacollection] dc join [fcpp_hpsd].[dbo].[datasharing] dp on dc.datapointid = dp.datapointid [datapointdate] >= ''' + convert(varchar(max), @startdate) + ''' , [datapointdate] < ''' + convert(varchar(max), @enddate) + ''' , dc.datapointid in (select [datapointid] [fcpp_hpsd].[dbo].[datasharing] filename = ''' + @filename + ''') ) source pivot ( sum(datapointvalue) columnname in ('+ @liststr +') ) pvt order datapointdate xml path(''' + 'datarow' + '''), root;' print @sqlcommand exec (@sqlcommand) end go the executed query looks this:
select * (select [datapointdate], dp.columnname, [datapointvalue] [fcpp_hpsd].[dbo].[vw_datacollection] dc join [fcpp_hpsd].[dbo].[datasharing] dp on dc.datapointid = dp.datapointid [datapointdate] >= 'jul 15 2013 12:00am' , [datapointdate] < 'jul 22 2013 12:00am' , dc.datapointid in (select [datapointid] [fcpp_hpsd].[dbo].[datasharing] filename = 'fdrd3')) source pivot ( sum(datapointvalue) columnname in ([fdrd3_kwh_a], [fdrd3_kwh_b], [fdrd3_kwh_c], [fdrd3_kwh], [fdrd3_i_a], [fdrd3_i_b], [fdrd3_i_c], [fdrd3_i_n], [fdrd3_v_a], [fdrd3_v_b], [fdrd3_v_c], [fdrd3_v_a-b], [fdrd3_v_b-c], [fdrd3_kv_c-a], [fdrd3_kw], [fdrd3_kva], [fdrd3_kvar], [fdrd3_kw_a], [fdrd3_kw_b], [fdrd3_kw_c], [fdrd3_kva_a], [fdrd3_kva_b], [fdrd3_kva_c], [fdrd3_kvar_a], [fdrd3_kvar_b], [fdrd3_kvar_c], [fdrd3_f], [fdrd3_iang_a], [fdrd3_iang_b], [fdrd3_iang_c], [fdrd3_iang_n], [fdrd3_vang_a], [fdrd3_vang_b], [fdrd3_vang_c], [fdrd3_vang_a-b], [fdrd3_vang_b-c], [fdrd3_vang_c-a], [fdrd3_pf_a], [fdrd3_pf_b], [fdrd3_pf_c], [fdrd3_pf], [fdrd3_pst_v_a], [fdrd3_pst_v_b], [fdrd3_pst_v_c], [fdrd3_plt_v_a], [fdrd3_plt_v_b], [fdrd3_plt_v_c], [fdrd3_vdev_a], [fdrd3_vdev_b], [fdrd3_vdev_c], [fdrd3_fdev], [fdrd3_thd_i_a], [fdrd3_thd_i_b], [fdrd3_thd_i_c], [fdrd3_thd_i_n], [fdrd3_thd_v_a], [fdrd3_thd_v_b], [fdrd3_thd_v_c]) ) pvt order datapointdate xml path('datarow'), root; so current procedure takes 35-65 seconds run. need see speeding procedure dealing timeouts. if can me out in can speed , rid of time being spent on index spool(eager spool) appreciate it.
edit 1:
i added sql fiddle helps.
here pivot unwound -- see if runs faster (i bet because of optimization of cte), if can re-write generator create query looks this:
with datelist ( select datapointid, filename, datapointvalue [fcpp_hpsd].[dbo].[datasharing] datapointdate >= @startdate , datapointdate < @enddate , filename = @filename ) select sum( j1.datepointvalue) sum_fdrd3_kwh_a sum( j2.datepointvalue) sum_fdrd3_kwh_b sum( j3.datepointvalue) sum_fdrd3_kwh_c sum( j4.datepointvalue) sum_fdrd3_kwh sum( j5.datepointvalue) sum_fdrd3_i_a sum( j6.datepointvalue) sum_fdrd3_i_b sum( j7.datepointvalue) sum_fdrd3_i_c sum( j8.datepointvalue) sum_fdrd3_i_n sum( j9.datepointvalue) sum_fdrd3_v_a sum(j10.datepointvalue) sum_fdrd3_v_b sum(j12.datepointvalue) sum_fdrd3_v_c sum(j13.datepointvalue) sum_fdrd3_v_a_b sum(j14.datepointvalue) sum_fdrd3_v_b_c sum(j15.datepointvalue) sum_fdrd3_kv_c_a sum(j16.datepointvalue) sum_fdrd3_kw sum(j17.datepointvalue) sum_fdrd3_kva sum(j18.datepointvalue) sum_fdrd3_kvar sum(j19.datepointvalue) sum_fdrd3_kw_a sum(j20.datepointvalue) sum_fdrd3_kw_b sum(j21.datepointvalue) sum_fdrd3_kw_c sum(j22.datepointvalue) sum_fdrd3_kva_a sum(j23.datepointvalue) sum_fdrd3_kva_b sum(j24.datepointvalue) sum_fdrd3_kva_c sum(j25.datepointvalue) sum_fdrd3_kvar_a sum(j26.datepointvalue) sum_fdrd3_kvar_b sum(j27.datepointvalue) sum_fdrd3_kvar_c sum(j28.datepointvalue) sum_fdrd3_f sum(j29.datepointvalue) sum_fdrd3_iang_a sum(j20.datepointvalue) sum_fdrd3_iang_b sum(j31.datepointvalue) sum_fdrd3_iang_c sum(j32.datepointvalue) sum_fdrd3_iang_n sum(j33.datepointvalue) sum_fdrd3_vang_a sum(j34.datepointvalue) sum_fdrd3_vang_b sum(j35.datepointvalue) sum_fdrd3_vang_c sum(j36.datepointvalue) sum_fdrd3_vang_a_b sum(j37.datepointvalue) sum_fdrd3_vang_b_c sum(j38.datepointvalue) sum_fdrd3_vang_c_a sum(j39.datepointvalue) sum_fdrd3_pf_a sum(j40.datepointvalue) sum_fdrd3_pf_b sum(j41.datepointvalue) sum_fdrd3_pf_c sum(j42.datepointvalue) sum_fdrd3_pf sum(j43.datepointvalue) sum_fdrd3_pst_v_a sum(j44.datepointvalue) sum_fdrd3_pst_v_b sum(j45.datepointvalue) sum_fdrd3_pst_v_c sum(j46.datepointvalue) sum_fdrd3_plt_v_a sum(j47.datepointvalue) sum_fdrd3_plt_v_b sum(j48.datepointvalue) sum_fdrd3_plt_v_c sum(j49.datepointvalue) sum_fdrd3_vdev_a sum(j50.datepointvalue) sum_fdrd3_vdev_b sum(j51.datepointvalue) sum_fdrd3_vdev_c sum(j52.datepointvalue) sum_fdrd3_fdev sum(j53.datepointvalue) sum_fdrd3_thd_i_a sum(j54.datepointvalue) sum_fdrd3_thd_i_b sum(j55.datepointvalue) sum_fdrd3_thd_i_c sum(j56.datepointvalue) sum_fdrd3_thd_i_n sum(j57.datepointvalue) sum_fdrd3_thd_v_a sum(j58.datepointvalue) sum_fdrd3_thd_v_b sum(j59.datepointvalue) sum_fdrd3_thd_v_c [fcpp_hpsd].[dbo].[vw_datacollection] dc left join datelist j1 on dc.datapointid = j1.datapointid , j1.columnname = 'fdrd3_kwh_a' left join datelist j2 on dc.datapointid = j2.datapointid , j2.columnname = 'fdrd3_kwh_b' left join datelist j3 on dc.datapointid = j3.datapointid , j3.columnname = 'fdrd3_kwh_c' left join datelist j4 on dc.datapointid = j4.datapointid , j4.columnname = 'fdrd3_kwh' left join datelist j5 on dc.datapointid = j5.datapointid , j5.columnname = 'fdrd3_i_a' left join datelist j6 on dc.datapointid = j6.datapointid , j6.columnname = 'fdrd3_i_b' left join datelist j7 on dc.datapointid = j7.datapointid , j7.columnname = 'fdrd3_i_c' left join datelist j8 on dc.datapointid = j8.datapointid , j8.columnname = 'fdrd3_i_n' left join datelist j9 on dc.datapointid = j9.datapointid , j9.columnname = 'fdrd3_v_a' left join datelist j10 on dc.datapointid = j10.datapointid , j10.columnname = 'fdrd3_v_b' left join datelist j12 on dc.datapointid = j12.datapointid , j12.columnname = 'fdrd3_v_c' left join datelist j13 on dc.datapointid = j13.datapointid , j13.columnname = 'fdrd3_v_a-b' left join datelist j14 on dc.datapointid = j14.datapointid , j14.columnname = 'fdrd3_v_b-c' left join datelist j15 on dc.datapointid = j15.datapointid , j15.columnname = 'fdrd3_kv_c-a' left join datelist j16 on dc.datapointid = j16.datapointid , j16.columnname = 'fdrd3_kw' left join datelist j17 on dc.datapointid = j17.datapointid , j17.columnname = 'fdrd3_kva' left join datelist j18 on dc.datapointid = j18.datapointid , j18.columnname = 'fdrd3_kvar' left join datelist j19 on dc.datapointid = j19.datapointid , j19.columnname = 'fdrd3_kw_a' left join datelist j20 on dc.datapointid = j20.datapointid , j20.columnname = 'fdrd3_kw_b' left join datelist j21 on dc.datapointid = j21.datapointid , j21.columnname = 'fdrd3_kw_c' left join datelist j22 on dc.datapointid = j22.datapointid , j22.columnname = 'fdrd3_kva_a' left join datelist j23 on dc.datapointid = j23.datapointid , j23.columnname = 'fdrd3_kva_b' left join datelist j24 on dc.datapointid = j24.datapointid , j24.columnname = 'fdrd3_kva_c' left join datelist j25 on dc.datapointid = j25.datapointid , j25.columnname = 'fdrd3_kvar_a' left join datelist j26 on dc.datapointid = j26.datapointid , j26.columnname = 'fdrd3_kvar_b' left join datelist j27 on dc.datapointid = j27.datapointid , j27.columnname = 'fdrd3_kvar_c' left join datelist j28 on dc.datapointid = j28.datapointid , j28.columnname = 'fdrd3_f' left join datelist j29 on dc.datapointid = j29.datapointid , j29.columnname = 'fdrd3_iang_a' left join datelist j20 on dc.datapointid = j20.datapointid , j20.columnname = 'fdrd3_iang_b' left join datelist j31 on dc.datapointid = j31.datapointid , j31.columnname = 'fdrd3_iang_c' left join datelist j32 on dc.datapointid = j32.datapointid , j32.columnname = 'fdrd3_iang_n' left join datelist j33 on dc.datapointid = j33.datapointid , j33.columnname = 'fdrd3_vang_a' left join datelist j34 on dc.datapointid = j34.datapointid , j34.columnname = 'fdrd3_vang_b' left join datelist j35 on dc.datapointid = j35.datapointid , j35.columnname = 'fdrd3_vang_c' left join datelist j36 on dc.datapointid = j36.datapointid , j36.columnname = 'fdrd3_vang_a-b' left join datelist j37 on dc.datapointid = j37.datapointid , j37.columnname = 'fdrd3_vang_b-c' left join datelist j38 on dc.datapointid = j38.datapointid , j38.columnname = 'fdrd3_vang_c-a' left join datelist j39 on dc.datapointid = j39.datapointid , j39.columnname = 'fdrd3_pf_a' left join datelist j40 on dc.datapointid = j40.datapointid , j40.columnname = 'fdrd3_pf_b' left join datelist j41 on dc.datapointid = j41.datapointid , j41.columnname = 'fdrd3_pf_c' left join datelist j42 on dc.datapointid = j42.datapointid , j42.columnname = 'fdrd3_pf' left join datelist j43 on dc.datapointid = j43.datapointid , j43.columnname = 'fdrd3_pst_v_a' left join datelist j44 on dc.datapointid = j44.datapointid , j44.columnname = 'fdrd3_pst_v_b' left join datelist j45 on dc.datapointid = j45.datapointid , j45.columnname = 'fdrd3_pst_v_c' left join datelist j46 on dc.datapointid = j46.datapointid , j46.columnname = 'fdrd3_plt_v_a' left join datelist j47 on dc.datapointid = j47.datapointid , j47.columnname = 'fdrd3_plt_v_b' left join datelist j48 on dc.datapointid = j48.datapointid , j48.columnname = 'fdrd3_plt_v_c' left join datelist j49 on dc.datapointid = j49.datapointid , j49.columnname = 'fdrd3_vdev_a' left join datelist j50 on dc.datapointid = j50.datapointid , j50.columnname = 'fdrd3_vdev_b' left join datelist j51 on dc.datapointid = j51.datapointid , j51.columnname = 'fdrd3_vdev_c' left join datelist j52 on dc.datapointid = j52.datapointid , j52.columnname = 'fdrd3_fdev' left join datelist j53 on dc.datapointid = j53.datapointid , j53.columnname = 'fdrd3_thd_i_a' left join datelist j54 on dc.datapointid = j54.datapointid , j54.columnname = 'fdrd3_thd_i_b' left join datelist j55 on dc.datapointid = j55.datapointid , j55.columnname = 'fdrd3_thd_i_c' left join datelist j56 on dc.datapointid = j56.datapointid , j56.columnname = 'fdrd3_thd_i_n' left join datelist j57 on dc.datapointid = j57.datapointid , j57.columnname = 'fdrd3_thd_v_a' left join datelist j58 on dc.datapointid = j58.datapointid , j58.columnname = 'fdrd3_thd_v_b' left join datelist j59 on dc.datapointid = j59.datapointid , j59.columnname = 'fdrd3_thd_v_c'
Comments
Post a Comment