5

Is there any way to pre compile stored procedures in SQL Server? My requirement goes like this.. I have some stored procedures, which take more time in compiling than executing. So I want to precompile all the stored procedures. It would be nice to precompile them when the db server is started and running.

Any ideas on this would be greatly helpful!

RBT
  • 24,161
  • 21
  • 159
  • 240
fr21
  • 1,746
  • 7
  • 26
  • 45
  • 1
    are you sure you are not micro optimising here something that is not really needed, ive never seen a need for something like this in the real world – Sam Saffron Jun 26 '09 at 11:49
  • I have a table with hundreds of thousands of records. So enabled indexed views on it for quicker access of data. This is a downside for insertion and updation on that table. So my stored procedure which is responsible for insertion takes a lot of time, when it is executed for first time. But later on it is quicker. I found that sp is taking time in compilation and preparation of execution plans. Once it is compiled it is fast enough to insert records. Can I have this fast performance first time itself by precompiling it? – fr21 Jun 26 '09 at 11:59
  • 1
    Do you realy have an indexed view on one single table? If yes then create an index on the table, dont create an indexed view. – Mathias F Jun 26 '09 at 21:21

6 Answers6

7

Write a script that executes each stored procedure using "SET FMTONLY ON".

The procedure will be compiled, but no permanent changes will be made to the DB during execution. You can't use this with procedures that use temporary tables (#table syntax).

That's how Microsoft does it in Visual Studio to determine what the output of your stored procedure should be.

Carter Medlin
  • 11,857
  • 5
  • 62
  • 68
3

you can force a recompile, but it will not happen until the next time it is run

EXEC SP_RECOMPILE YourProcedureName

more info here...

force it to recompile each time it is run:
CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....

force it to recompile this time:
EXEC YourProcedureName WITH RECOMPILE

here is a good article on Optimizing SQL Server Stored Procedures to Avoid Recompiles

and another...

EDIT based on OP's comments:

why don't you manually run it (outside the application) with bogus data (not so bogus that the execution plan is bad though, google: sql server parameter spoofing and sniffing) the first time, that can force a compile, then run some sql to delete out what was inserted. when the users run it for the first time, it will have already run and have been compiled.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • I tried using sp_Recompile option. But when the sp is executed for first time, it takes a lot of time, and later on it is quick enough. How can i improve it for first time use? – fr21 Jun 26 '09 at 12:04
1

If you are using SQL Server 2008 then you may be able to use a Plan Guide in order to enforce the re-use of an existing, pre compiled, Execution Plan.

See Understanding Plan Guides, for more details and in particular read "OBJECT Plan Guides"

I suspect however that the source of your issue is the process logic being implemented within your stored procedure and would suggest this as your first point of review for performance tuning.

Community
  • 1
  • 1
John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • Hi, I am using Sql Server 2005, and in the stored procedure contains a plain insert statement. Index views exists on the table. So preparing execution plan is consuming time. Any idea how we can prepare all this ahead and keep sp ready for execution? – fr21 Jun 26 '09 at 19:27
  • 1
    Hi, in order to help with my understanding of your environment, Why are you using indexed views rather than adding indexes directly to the underlying table? How many indexed views are you using? How many indexes are there on the table? – John Sansom Jun 26 '09 at 20:35
  • 1
    This realy sounds like the problem. Indexed Views are often misunderstood as just another index. But they realy create a phyiscal representation of the view. If you insert into a table that is part of an indexed view you are asking for trouble. If this is part of a regular import routine, then before importing disable the index. After import enable again. – Mathias F Jun 26 '09 at 21:15
1

Is it possible to just execute the SP once without affecting any data? If so, you can then probably find a way to trigger this SP on server start up.

Paweł Czopowik
  • 305
  • 1
  • 11
0

A stored procedure should only compile (and for that matter only create a query plan) when it is created and first executed.

If you are using WITH RECOMPILE a lot, you should stop. If you're doing that to force recalculation of query plans because different parameters work more efficiently with different query plans (and if that matters, performance-wise) then you need to consider creating different SPs for the different query plans, perhaps with a "parent" SP to decide which to call. But it's not a pain-free exercise.

If your tables are really in the sub-million row category then I'd look most carefully at indexing and keeping statistics up to date, recompiling periodically at quiet times to keep the query plans efficient. Once you're into tens or hundreds of millions of rows then there may be a case for going through the pain of duplication.

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
-1

Seldom is compilation time significant these days. Insert this code in the top of your program and see for yourself just how little time compilation takes.

SET STATISTICS TIME ON
GO

You will find compilation times are typically given at 0ms, which means too small to bother with, whereas execution times are in the tens, hundreds, or even thousands of milliseconds.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
David F Mayer
  • 1
  • 3
    Regardless of whether you are right, if you are not going to answer the specific question that the OP asked, it should be in a comment, not an answer. – Ansel Santosa Jan 22 '15 at 22:57