SQL Server Performance Forum – Threads Archive

Speeding up Initial call to stored proc

The first time my stored proc is called from within my .Net WinForm application, it takes along time. Subsequent calls to this stored procedure is much much faster. It is a complicated query within the stored proc with four tables and a dynamic where clause in that most if not all parameters are optional. Is there a key word I could use to speed it, maybe some hints….
Any advise or suggestion.

Hi ya, it depends what the slowness is caused by… if you run the proc adding the ‘with recompile’ clause at the end of the call, does it run as slow as it did the first time? if so, then it looks like the performance penalty might be in the compilation of the proc itself. The only way to work around this would be to try to simplify the stored procedure as much as possible and if there are parts that are almost never called, then put them in a subprocedure. otherwise it could be because SQL server caches dynamic plans, so that if it sees the same dynamic plan the next time it won’t recompile it again. There is no easy way to work around this other than simplifying the query and/or not using dynamic sql if you close the app and then restart it (so without doing anything to sql) and it is still slow on its first call then it could be due to overhead in .net having to connect to the database, etc. lastly it can be because the first time since sql is started it has to get info from disk. physical reads being much slower than logical ones. This could be improved by tuning the queries and ensuring that appropriate indexes are in place not sure if that helped or not… Twan

Thanks for the ideas and I’ll look into them

]]>