Post reply

sp recompilation, how it can be avoided?

  • May 26, 2009 at 2:37 am

    #999752

    Yes , I used your suggestions , and the sp I just posted is recompiled 40 000 times a day. And I think this is a problem. I know that the option modifies the threshold of the temp tables making it like for normal tables.And analyzing the profiler, I saw that in all the recompiled statements I had temporary tables, so I'm trying as I can to remove them. I posted the sp because I'm sure that the dynamic sql is the big problem, but I don't think I can remove it. At least I hope to reduce the number of statements that recompile, so any suggestion about rewriting the sp is accepted gladly :-D.

  • May 26, 2009 at 2:39 am

    #999753

    shnex (5/26/2009)

    If you have any questions about the logic...I can help you this time 🙂

    I wasn't exactly planning on rewriting it for you. I was more suggesting that you look at it with a view to removing the temp tables, if possible.

    I'll pull it into management studio later and have a look, but for now, what does this do?

    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

  • May 26, 2009 at 3:12 am

    #999770

    Of course...I wait for your suggestions....

    The sp lists some 'activities' from Wf_Activity table and receives from the UI a whereClause that filters by an organization, workdate, etc.After this the records are filtered again.

    This listing is made by pages, the number of rows on a page and the page number is send as a parameter.

    If the @work_Date parameter is 1 the ordering of the records is made listing first the ones that have work_Date null or empty...

    Thanks again for the help...

  • May 26, 2009 at 3:52 am

    #999782

    Having looked at the procedure in detail, I would very much encourage you to read:

    http://www.sommarskog.se/dyn-search-2005.html

    That should give you some ideas.

    Cheers,

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 26, 2009 at 4:06 am

    #999793

    Also, for ideas on efficient paging when using a custom ORDER BY clause, take a look at the following SSC article:

    http://www.sqlservercentral.com/articles/Advanced+Querying/3181/

    There are many similar works on the interweb, as indexed by Google.

    Hope that helps - longer term.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 5 posts - 16 through 19 (of 19 total)

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