Set an SQLCommand timeout default application wide?

  • Thread starter Thread starter Stu Carter
  • Start date Start date
S

Stu Carter

Hi,

ENV: Windows 2003, Visual Studio.Net 2003, .Net Framework 1.1

We have a rather large application that uses ADO.Net to access a SQL
database. As the database grows, we are seeing SQLCommand timeouts. So we
added code to configure the SQLCommand.CommandTimeout property in areas
where we deal with potentially large amounts of data.

However, we are now seeing other areas of the product becoming prone to
Command timeouts. So, the question is - without having to add code to
explicitly change every SQLCommand.CommandTimeout, is there any way to
change the default value from 30 seconds? Using an app.config setting or
regkey etc?

All those code changes would be painful, but additionally, a version has
already shipped to customers, so we don't want to be distributing a large
patch for this if we can avoid it.

I have searched Google groups and MSDN, but no joy.

Thanks for your help,
Stuart
 
Hi Stu,

You might use a helper method that creates SqlCommand for you and sets
timeout to a desired value.
 
Stu:

Unfortunately I can't think of any global setting that will take care of
this b/c I the default is 30 seconds and I don't believe it's read out of
any config settings. For your next app, you may want to centralize your
data access code and separate it in a .dll b/c then you could make mods a
lot easier. I know that does nothing for your current situation.

Another thing though Stu....if you have a lot of queries that are taking
more than 30 seconds to complete..that's a lot of time if a user is waiting
and a good amount of time in general. I'd suggest looking at the indexes
and getting intimate with profiler to see if I could speed these up b/c in
most many instances,chaning the command timeout is a bandaid and it tends to
exacerbate itself as time goes on. If you could come up with an indexing
strategy internally, you could send a patch that just does alter tables
building the indexes and that might help quite a bit. As to your original
question though, I think you are going to have to manually replace things
b/c AFAIK, there's not setting that's going to change that for you.

Bill
 
Thanks Miha,

If we can't find another way, there are two viable options:

1) Create a helper class with a static method that takes an SQLCommand and
sets its timeout.

2) Ideally, we would inherit from SQLCommand and set the timeout in the
derived class, but we make heavy use of the component designer for creating
commands and that only creates SQLCommands, so a certain amount of
complicated regexp Find and replace is in order :)

Cheers,
Stu

Miha Markic said:
Hi Stu,

You might use a helper method that creates SqlCommand for you and sets
timeout to a desired value.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Stu Carter said:
Hi,

ENV: Windows 2003, Visual Studio.Net 2003, .Net Framework 1.1

We have a rather large application that uses ADO.Net to access a SQL
database. As the database grows, we are seeing SQLCommand timeouts. So we
added code to configure the SQLCommand.CommandTimeout property in areas
where we deal with potentially large amounts of data.

However, we are now seeing other areas of the product becoming prone to
Command timeouts. So, the question is - without having to add code to
explicitly change every SQLCommand.CommandTimeout, is there any way to
change the default value from 30 seconds? Using an app.config setting or
regkey etc?

All those code changes would be painful, but additionally, a version has
already shipped to customers, so we don't want to be distributing a large
patch for this if we can avoid it.

I have searched Google groups and MSDN, but no joy.

Thanks for your help,
Stuart
Click to expand...
Click to expand...
 
Hi Bill,

Thanks for your suggestions.

Just to add little more...

I agree that Commands taking such time are prime candidates for
optimisation, and we have done some work on the Indexes, but I fear it is
diminishing returns from here on in...we'll have to see what our peformance
team can do!

The commands that timeout are typically updating/inserting 100's of
thousands of records (500,000+ is not uncommon). This is all happening in a
service as a background task, so the users don't really perceive the
performance hit, and they expect such large operations to take some time.
Performing commands in batches etc might help.

I wonder if anyone from Microsoft knows of a hidden key to solve the
immediate problems! :)

Cheers,
Stu
 
Hi Stu,

Stu Carter said:
Thanks Miha,

If we can't find another way, there are two viable options:

1) Create a helper class with a static method that takes an SQLCommand and
sets its timeout.
Right.

2) Ideally, we would inherit from SQLCommand and set the timeout in the
derived class, but we make heavy use of the component designer for creating
commands and that only creates SQLCommands, so a certain amount of
complicated regexp Find and replace is in order :)
Click to expand...

Unfortunatelly nope. The problem here is, otherwise I would recommend it,
that SqlCommand is a sealed class.
Thus no deriving fun there. :)
 
Thanks for Bill and Miha's quick response!

Hi Stu,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to change the default value of
CommandTimeout from 30 seconds. If there is any misunderstanding, please
feel free to let me know.

I'm afraid, there isn't a global setting that can change this default
value. As this behavior is by design in the .NET framework, I don't think
we can change it without explicit coding.

My suggestion is to centralize your data access just as Bill mentioned. You
can use a static method in a class to return a new command object whose
CommandTimeout value has been set to a value you expect. The value can be
put in app.config file. However, I know, it does not help in your current
situation.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Stu,

I'm struggling with the same issue.

Here's a idea - use a function to call back the number of rows in key
tables; then change the timeout dynamically based upon the returned values.
Yes, it does require that each command have a code setting for timeout, but
that's trivial; thereafter, you will always have a good timeout because it
will be being set dynamically. Of course, if you want to take a bit of
chance with a app hanging, you can always set timeout to 0, which will run
as long as it has to to complete the task.

HTH,

Bernie Yaeger
 
Back
Top