dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: Recompile SQL Server Stored Procedure

  1. #1
    Join Date
    Jan 2013
    Posts
    85

    Recompile SQL Server Stored Procedure

    I have SP which is used to select some records with set of filters and UNION. One of the filters is a date range.
    Usually users of VB6 application which uses this SP used to select pretty narrow data range, no more than 1 month.
    Resently one of the users set 1 year date range and got timeout error.
    I set CommandTimeout = 0 to find out how long does it take if not interrupted. 110 seconds. Wow!
    I started to look for a way to make SP faster. I commented the UNION and following Select, compiled and ran the process - just 2 seconds. I uncommented the second Select and commented the first one, compiled - 2 seconds.
    I returned both, so restored the original text of SP, compiled, ran the process from VB and got the result very fast, within same 2 seconds.
    So, I think recompiling fix the problem.
    I do not know what happened to that SP due to my weak knowledg of the database stuff. I cannot replicate this situation to make sure that recompiling solves the problem.
    Is there any way to recompile all SPs in the database to avoid timeout errors? I read on WEB that it is possible to include WITH RECOMPILE statement into SP, but I'm afraid to break anything else.

    Thank you

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,248

    Re: Recompile SQL Server Stored Procedure

    Read up and try running these two commands:
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS

  3. #3
    Join Date
    Jan 2013
    Posts
    85

    Re: Recompile SQL Server Stored Procedure

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)