Post reply

Need to avoid table spool in view

  • August 3, 2011 at 1:30 pm

    #245578

    hi

    i have created view which is running slow,i saw execution plan and it seems table spool takes 50%,every other thing is fine.

    How to avoid table spool,any advice

    Thanks

  • August 3, 2011 at 1:36 pm

    #1363303

    Please attach the actual execution plan in a new message (.sqlplan).

  • August 3, 2011 at 1:47 pm

    #1363308

    Are you using a recursive CTE? In that case, the table spool is inevitable.

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

  • August 3, 2011 at 1:51 pm

    #1363311

    Table spools are generally optimisations so that the execution engine doesn't have to go back and recalculate things.

    Sure the costs are correct and the table spool really is the problem?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 3, 2011 at 2:51 pm

    #1363349

    hi

    i am seeing table spool 50% and keylookup 69%

    so i think this both can be problem

  • August 3, 2011 at 3:07 pm

    #1363359

    Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 3, 2011 at 6:09 pm

    #1363408

    hi

    i cant put my all execution plan,all oher field is 0%,except table spool and key lookup.

    i dont understand how to increase my time

  • August 3, 2011 at 9:18 pm

    #1363421

    daveriya (8/3/2011)

    hi

    i cant put my all execution plan,all oher field is 0%,except table spool and key lookup.

    i dont understand how to increase my time

    daveriya,

    Without any additional information, the best suggestion we could probably give you is to add "WITH SCHEMA BINDING" to the view definition and make sure you don't have a recursive CTE in the code.

    If you really want help on this, the only way folks can figure out what's wrong is to see the Actual Execution Plan. See the article that Gila Monster provided a link for to learn how to save a copy of the execution plan and then attach it to your next post. Posting the code for the View would be extremely helpful, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 4, 2011 at 7:07 am

    #1363617

    hi

    the thing is i cannot show my data ,its confidential ,i can show my execution plan,but when i save it comes with query,is there any way i can show only my execution plan

  • August 4, 2011 at 7:14 am

    #1363623

    daveriya (8/4/2011)

    hi

    the thing is i cannot show my data ,its confidential ,i can show my execution plan,but when i save it comes with query,is there any way i can show only my execution plan

    We can understand that. Show SAMPLE data. Juste make sure if covers all your needs in the query and then show the required output from that sample data.

    That way we all speak the same language and see the same thing > math & logic.

  • August 4, 2011 at 7:39 am

    #1363643

    hi

    i am attaching my query part in doc.plz see it and reply me asap

  • August 4, 2011 at 7:43 am

    #1363651

    GilaMonster (8/3/2011)

    Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Nope, read the previous and do that, we can't help without ALL that info.

  • August 4, 2011 at 7:51 am

    #1363658

    if you dont want to help,dont comment

  • August 4, 2011 at 7:56 am

    #1363662

    daveriya (8/4/2011)

    if you dont want to help,dont comment

    If intend to treat us that way you better find another forum to get help.

  • August 4, 2011 at 8:06 am

    #1363665

    daveriya (8/4/2011)

    if you dont want to help,dont comment

    If you want help, read the article that explains what we need to solve this kind of problem. Screenshot of execution plan != execution plan

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 18 total)

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