Re: Set an SQLCommand timeout default application wide?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Stu Carter (Stu.Carter_at_somekindofcompany.co.uk)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 16:34:29 -0000

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

"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:OsxvcH3BEHA.2600@TK2MSFTNGP09.phx.gbl...
> 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
> "Stu Carter" <Stu.Carter@somekindofcompany.co.uk> wrote in message
> news:ulrwXr2BEHA.2360@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> >
>
>

Relevant Pages