I'm testing a stored procedure on SQL Server 2008 R2 Express. Eventually it will be run in production on a full version of SQL Server 2008 R2.
Testing the stored procedure in Management Studio I notice that the first time I run it after a restore of the database or a restart of the SQL Server service the procedure takes about 35 seconds to run. The second and subsequent time it completes in a blink of an eye.
From this I assume that the query plan takes a while to be created. The symptoms seem to match with what causes the query plan to be recreated.
My problem is when I execute this stored procedure from my C# application if it goes over 30 seconds it times out. I think I have figured out how to increase the timeout but from all the threads I have read people say that in all but exceptional circumstances 30 seconds should be heaps of time. "Fix the problem not increase the timeout".
As this stored procedure will only be run once per month the chances are pretty good that it will create a new query plan every time it is used.
None of my other stored procedures have this problem.
Any ideas on the best way to diagnose the real issue?
The procedure isn't hugely complicated, it reads from a couple of joined tables and inserts about 4000 rows into another table based on a few conditions. I'm not the best SQLer in the world so maybe I have done some silly things.
I can't really add indexes to the database or anything as it belongs to a business application written by an external company. So I'm very cautious making any changes as it may cause other unforseen problems.
Let me know if you think it is worth posting my sql code here.
Thanks David
Looks like I answered my own question. I clicked on the little "Display Estimated Query Plan" button in Management Studio and took a look at the query plan. In nice big letters it was recommending that I ad an index to a table and provided me the exact code short of an index name that I needed to use to create it. I took a look at that table and it has over 3 million rows in it. Not a lot in todays terms but obviously a problem if you have to keep reading it end to end which it appears to do while trying to create the query plan.
Anyway I created the index and run my application again and I can't reproduce the problem. It runs like a bullet every time.
I guess I just need to think about the consequences of me adding an index. First will be to give it a name that the external company is never likely to duplicate. :)
Thanks to all who took the time to read my post and I hope this answer helps someone else.
David