SQL Server Performance Forum – Threads Archive
Avoiding Table Spool/Eager SpoolThe 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 )
SELECT (CASE WHEN (T1.TRAYS < 0 OR Record_type_id = 3) THEN ‘C’ ELSE ‘I’ END) AS INV_TYPE
,T1.TRF_CTH_WGT_FLAG — Transfer price catch weight indicator
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.
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? —
Microsoft SQL Server MVP