Post reply

How can I avoid Eager Spool on my Update Statement which consumes 56% of total cost

  • July 29, 2013 at 6:58 am

    #395217

    Hi Guys,

    I found my Update Query is getting the usage of Eager Spool, and the temp table (implicit) cost about 56% percentage of the Update Statement.

    Kindly Advice how to over come this scenario.

    Here is the Query.

    --table structure:

    create table mytable(tableid nchar(25), element1 int, element2 smallint, col4 nvarchar(255))

    ;Merge [dbo].[mytable] as tr --having a 10 million records

    using #mytable as tmp --carries the data to be updated and about 2500 rows.

    on(tr.tableid = tmp.tableid and tr.element1 =tmp.element1 and tr.element2 = tmp.element2)

    When Matched Then

    update set

    tr.tableid = tmp.tableid,

    tr.element1 = tmp.element1,

    tr.element2 = tmp.element2 ,

    tr.col4 = tmp.col4,

    tr.modifiedby = @userid,tr.modifieddate = getdate()

    when not matched then

    insert (tableid,element1,element2,col4,createdby,createddate)

    values (tmp.tableid,tmp.element1,tmp.element2,tmp.col4,@userid,getdate());

    the attached showplanxml_mytable.txt file could help you in better way.

    Looking forward to hear from you soon Guys..

    Thanks,

    Prabhu

  • July 29, 2013 at 7:11 am

    #1636623

    Hi Guys,

    I got the Answer from this thread.

    http://www.sqlservercentral.com/Forums/Topic1442095-1292-1.aspx

    Thanks to Gila Monster

    I just removed some unwanted indexes from my table and found that the Eager Spool has been removed from the Execution plan (do the same in your scenario only if the indexes were not used any where...)

    Thanks,

    Prabhu

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply