December 11, 2011 at 10:27 pm
Hi,
I am using one stored procedure. I am calling the procedure using dbcommand object in vb.net. Normally it takes 5 min to complete without timeout error. But when i was getting the timeout error while executing this procedure it was coming in the next second(withing one second). Actually the time out error should come after the timeout period configured in the dbcommand.timeout. Because i am writing the logs in log file. before calling the procedure i am writing the time in log file. same i am doing after finishing the procedure also. but if timeout error is not available then the time difference in the log file is 5 min. if the timeout error occurs then the time difference in log file is one second only. how it is possible.
Can anybody help on the same.
Regards,
Vaithilingam.K
December 11, 2011 at 10:35 pm
what value have you set for dbcommand.timeout in the .Net code ?
December 11, 2011 at 10:47 pm
if timeout error is not available then the time difference in the log file is 5 min. if the timeout error occurs then the time difference in log file is one second only. how it is possible.
You see only 1 sec time difference because the procedure doesn't execute at all ! It keeps waiting to get a response for its query, but then timeout expires because SQL Server doesn't return any response in that specified time period.
December 11, 2011 at 11:06 pm
I had set timeout as 30000 in vb.net code
December 11, 2011 at 11:14 pm
Hi Sujeet,
As per your point before executing any procedure or sql statement it has to get the response from server. ok. but how can i increase the response time in this. If i increase it or make it as infinite i think it will work. Please tell me how to do this.
regards,
Vaithilingam.K
December 11, 2011 at 11:23 pm
vaithi.saran846 (12/11/2011)
Hi Sujeet,As per your point before executing any procedure or sql statement it has to get the response from server. ok. but how can i increase the response time in this. If i increase it or make it as infinite i think it will work. Please tell me how to do this.
regards,
Vaithilingam.K
IMO you should not focus on setting the timeout at this moment. See if you can optimize your query. Timeout is appearing because SQL Server estimation / execution are exceeding timeout setting.
Please post your query here with DDL, sample data & execution plan for better assistance.
December 11, 2011 at 11:34 pm
vaithi.saran846 (12/11/2011)
how can i increase the response time in this. If i increase it or make it as infinite i think it will work.regards,
Vaithilingam.K
Yes it will work. However, I believe that this procedure is not written for any web site OR online processing. Because nobody would wait for a web page to open in 5 minutes.
Therefore, as Dev said above, look for the areas in your query which you can fine tune.
If it is for some offline data processing (& you have made a windows or console application for that) then you can set dbcommand.timeout = 0 to set the timeout as infinte.
December 11, 2011 at 11:46 pm
Divine Flame (12/11/2011)
vaithi.saran846 (12/11/2011)
how can i increase the response time in this. If i increase it or make it as infinite i think it will work.regards,
Vaithilingam.K
Yes it will work. However, I believe that this procedure is not written for any web site OR online processing. Because nobody would wait for a web page to open in 5 minutes.
Therefore, as Dev said above, look for the areas in your query which you can fine tune.
If it is for some offline data processing (& you have made a windows or console application for that) then you can set dbcommand.timeout = 0 to set the timeout as infinte.
Just adding a word of caution. Setting the timeout to infinite is NOT a good practise.
December 11, 2011 at 11:46 pm
Hi Dev,
Here actual problem is procedure is not yet executed. it is waiting for response from the server to execute. As per your point if i set timeout =0 in vb.net code, then i never get timeout error. As per Divine Flame point it just waiting to execute the procedure.
December 12, 2011 at 12:58 am
vaithi.saran846 (12/11/2011)
Hi Dev,Here actual problem is procedure is not yet executed. it is waiting for response from the server to execute. As per your point if i set timeout =0 in vb.net code, then i never get timeout error. As per Divine Flame point it just waiting to execute the procedure.
Please don't confuse yourself.
Let me rephrase your question. Your VB.Net is code is executing Stored Procedure which usually takes 5+ minutes. Sometimes it just error out within a second with ‘Time Out Error’. Off Course SQL server is not executing it but estimating that it will cross time out settings thus you are getting the error.
Per BOL:
Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Queries that have estimated run times greater than this limit, return an error and are not executed. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.
Why SQL Server Optimizer is estimating it? There could be many reasons for that and we can provide better help if you share DDL, Sample Data, Your Store Procedure Script & execution Plans.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply