Avoiding Table Spool/Eager Spool | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Avoiding Table Spool/Eager Spool

The following code imposes a Table Spool on the TBL_WH_PENDING which consumes 58% of the total cost. is there any way i can remove that spool or optimze this query? INSERT INTO dbo.TBL_WH_PENDING WITH ( TABLOCKX )
( INV_TYPE
,TBL_WH_ALLOCATIONS
,ALLOCATION_ID
,TRF_CTH_WGT_FLAG
,TRAYS
,UNITS_PER_TRAY
,TRAN_QTY
,STORE_CTH_WGT_FLAG
,STORE_SELL_PRI
,TRAY_WGT
,TRF_PRI
,RETAIL_FLAG
,TRAY_COST_PRI
,RECORD_TYPE_ID
,KEY_FIELD )
SELECT (CASE WHEN (T1.TRAYS < 0 OR Record_type_id = 3) THEN ‘C’ ELSE ‘I’ END) AS INV_TYPE
,T1.TBL_WH_ALLOCATIONS
,T1.ALLOCATION_ID
,T1.TRF_CTH_WGT_FLAG — Transfer price catch weight indicator
,T1.TRAYS
,T1.UNITS_PER_TRAY
,T1.TRAN_QTY
,T1.STORE_CTH_WGT_FLAG
,T1.STORE_SELL_PRI
,T1.TRAY_WGT
,T1.TRF_PRI
,T1.RETAIL_FLAG
,T1.TRAY_COST_PRI
,T1.RECORD_TYPE_ID
,T1.KEY_FIELD
FROM dbo.TBL_WH_WRK_ALLOCATIONS AS T1 WITH ( TABLOCKX )
INNER JOIN TBL_WH_NEW_3P_ALLOCATIONS T2 WITH (TABLOCKX)
ON T2.TBL_WH_ALLOCATIONS = T1.TBL_WH_ALLOCATIONS The table TBL_WH_PENDING has 40 columns and 30k rows. statistics are up-to-date and there are 7 indexes on this table. i could not run index tuning wizard as i am not the admin in that server.
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
1. write your own database engine
2. insert 1 row (or up to 100 or so) at a time the sql server optizer is doing its job, leave it alone unless you really know what you are not, not just pretending to
Looking at your statement, I don’t see anything obvious that can be optimized. Do you really need 7 indexes on that table? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>