SQL Server Performance

Avoiding Table Spool/Eager Spool

Discussion in 'T-SQL Performance Tuning for Developers' started by ramkumar.mu, Nov 14, 2006.

  1. ramkumar.mu New Member

    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..."
  2. joechang New Member

    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
  3. FrankKalis Moderator

    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

Share This Page