September 20, 2005 at 2:16 pm
Hi,
we are getting this error when we are calling a stored procedure thru front end (ASP.net). whenever we run the sp for the first time it takes around 30 sec and then the next subsequent run will be fast 2-3 secs. what could be the reason and we get the error on the fron end
Error: System.Data.SqlClient.SqlException: Timeout expired.
If anyone know the issue, it will be a great help.
-SN
September 20, 2005 at 2:37 pm
the first time you run it takes longer than subsequent times, thats usually an indication of a less than optimal query plan, but once the plan, or data gets in cache, the subsequent calls take less time.
You want to make sure and try to get the initial query time down by analyzing the query plan, and make sure you have proper indexes/database/query design.
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
http://www.sql-server-performance.com/rd_query_tuning.asp
If you want to post your code, and the text output of the execution plan someone may be able to point something out.
September 21, 2005 at 3:36 am
You can fix the orginal timeout by setting the SqlCommand.CommandTimeout property in your code, the default is 30 seconds. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclasscommandtimeouttopic.asp
But as Ray said you should definitely review the query plans and try and optimise.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply