SqlCommand CommandTimeout problem

Question

I need to be able to set the timeout to less than 1 second.  Say 75ms-250ms.  1000ms is huge for the application I'm working on.  Any ideas on how to accomplish this?  The CommandTimeout property is in seconds instead of ms.

Answer 1

Yes, SQLCommand timeout property is set in seconds and milliseconds.

No there is no way to change it to milliseconds, as per the documentation on MSDN.

However, you many try something like 0.25 seconds which is about 250 ms. I am not sure it will work though.

Answer 2

The problem with setting it to 0.25 is the CommandTimeout property is an int.  :(

Answer 3

Jonathan, Yes I should have looked that timeout property is an integer.

Most of the situation I ran into required  to increase timeout value from default 30 seconds to something larger.


Can you elaborate why do you want to set the timeout value less than a second..?

Answer 4

Yes.  My application deals with hardware in a real-time fashion (its kind of complicated).  I only have so much time to execute a sql stored procedure and get a result back.  I have a small window of about 100-200ms.  Once that time is finished.  I need to be able to timeout so that the next item can try to call the sproc.   I have spent a lot of time tuning my sql server calls.  Most calls execute in 30-75ms.  Occasionally one takes a long time (200-500ms) and causes problems because my thread is stuck blocking until it is complete.  I've been thinking about using the BeginExecuteReader() and a timer.  I'm thinking I could call Cancel() method if my callback routine isn't called in my 200ms timeout.

Answer 5

Hey Jonathan, Did you have any luck in the end? When I used a timer it caused problems when there where too many calls that where over the specified ms timeout. I suspect the problem is that it was having a hard time closing the thread that called the proc.