Table spool/Eager spool - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table spool/Eager spool

Author

Topic

srinath003
Starting Member

1 Post

Posted - 2007-11-08 : 15:25:24

Hi Every one can anyone help me out in optimizing the following query.

I can see that most of the cost involved in executing the query
if by table/eager spooling from the execution plan.

How can I optimize the query.

INSERT INTO [dbo].[cust_rev_summ_FXS_PROD_ALL_TEST]
select year_mth_nbr,ENTITY_ID,'' FAC_ID , '' natl_acct_nbr,
shp_pay_ind,service ,svc_cat_cd,svc_bas_cd,grnd_prod_grp_cd,sum(tot_shp_cnt) tot_shp_cnt,
sum(shp_wgt) shp_wgt,sum(shp_pce_qty) shp_pce_qty,sum(net_rev_amt) net_rev_amt,
sum(fuel_srchg_amt) fuel_srchg_amt
from
(
select ENTITY_ID,'' FAC_ID , NULL natl_acct_nbr, year_mth_nbr,
a.service ,svc_cat_cd,svc_bas_cd,grnd_prod_grp_cd,
'S' shp_pay_ind, sum(tot_shp_cnt) tot_shp_cnt,
sum(shp_wgt) shp_wgt,sum(shp_pce_qty) shp_pce_qty,sum(net_rev_amt) net_rev_amt,
sum(fuel_srchg_amt) fuel_srchg_amt
from dbo.cust_rev_summary a
INNER JOIN dbo.cust_profile_exp_9d b (index = idx_test_clu)
on a.cust_nbr= b.cust_nbr
WHERE year_mth_nbr in (select cast(month_yyyymm as integer) from dbo.rpt_curr_month_v_monthly
where LTRIM(RTRIM(LOWER(month_desc))) in ('cur','cur_1','cur_2','cur_3','cur_4','cur_5','cur_6',
'cur_7','cur_8','cur_9','cur_10','cur_11','cur_12'))
AND SHP_PAY_IND IN ('S','B') and entity_id <> ''
GROUP BY ENTITY_ID, year_mth_nbr,
service ,svc_cat_cd, shp_pay_ind,svc_bas_cd,grnd_prod_grp_cd

) x

GROUP BY ENTITY_ID, year_mth_nbr,service ,svc_cat_cd, shp_pay_ind,svc_bas_cd,grnd_prod_grp_cd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 15:46:46

This maybe

INSERT		[dbo].[cust_rev_summ_FXS_PROD_ALL_TEST]
SELECT		year_mth_nbr,
		ENTITY_ID,
		'' AS FAC_ID,
		NULL AS natl_acct_nbr, 
		'S' as shp_pay_ind,
		a.service,
		svc_cat_cd,
		svc_bas_cd,
		grnd_prod_grp_cd,
		sum(tot_shp_cnt) as tot_shp_cnt,
		sum(shp_wgt) as shp_wgt,
		sum(shp_pce_qty) as shp_pce_qty,
		sum(net_rev_amt) as net_rev_amt,
		sum(fuel_srchg_amt) as fuel_srchg_amt
from		dbo.cust_rev_summary as a
INNER JOIN	dbo.cust_profile_exp_9d as b with (index (idx_test_clu)) on b.cust_nbr = a.cust_nbr
WHERE		SHP_PAY_IND IN ('S', 'B')
		and entity_id > ''
		and exists	(
					select	*
					from	dbo.rpt_curr_month_v_monthly
					where	year_mth_nbr = month_yyyymm
						and month_desc like 'cur%'
				)
GROUP BY	year_mth_nbr,
		ENTITY_ID,
		a.service,
		svc_cat_cd,
		svc_bas_cd,
		grnd_prod_grp_cd

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources