Increase Connection\ Command timeout globally

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

environment : .net framework 1.1, ado.net, visual studio 2003, vb, sqlsever
2000, winforms, webforms

I want to increase the connection and command time out for all stored procs
in the application which might be around 200 or more and this woudl take me
forever if i do it indivisually, is there any way i can increase the ado.net
command timeout and connection timeout globally?

thanks
sameer
 
Ah, this is not a function of any SP code or anything done on the server.
Connection and Command timeout settings are done on the client. If you are
creating individual Connection objects for each open, I suggest using a
common Connection string that has the desired Connection timeout value. For
the Command objects, I suggest using a global variable to set the value, but
I expect you'll have to visit each Command object declaration and set the
timeout property.

My next question is why do you think this is necessary?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Bill , thanks for your suggestion, database is growning and also lot of logic
in Stored procs which is causing it to timeout. So are you sure that there is
not was i can define the default timeout of a command object in ay
configuration file like either app.config or web. config so that ado.net can
read it automatically and apply it ?

thanks
 
Ah, when my car's engine starts to overheat I don't start looking for ways
to add a bigger radiator, I look for a blown head gasket, a broken water
hose, better gas or reducing the load.

I suggest that you need to start working through your SPs and see why they
are running slowly. I discuss a number of these techniques in my book and
there are several other good sources of information in this regard. For
example if you have a complex SP, the query plan might not be optimal.
Simply breaking up the SP into several, more focused SPs that are called by
a "director" SP might make sense. Let the Query Analyzer test to see if you
need more (or fewer) indexes, make sure that the SQL server is not held back
by lack of RAM or queries that flush the cache or sharing CPU time with
Reporting Services or a print server.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
What William (Bill) Vaughn has said is correct.You must first try to speed up
the query.On one forum i found following

tips as to how to speed up query.

As far as the database connection timing out, if you have a lot of users you
may need to increase

the size of the connection pool. I think that the default is 20, maybe you
need more. The command

timeout is a SQL Server or possibly a networkproblem. Increasing the command
timeout is necessary

in some conditions like when you have a really big query that you know will
take a long time, but

it may be hiding a problem that should be addressed instead of worked around
with an increase

command timeout. I can not imagne that registering a user would be a complex
insert/update that

would justify increasing the command timeout. Here are some things you
should check:

1) check the performace of your queries/stored procs. If they are using
ineficient joins or doing

complex calculations you may need to do some tuning.
2) make sure you have proper indexes in place to speed your queries and
joins.
3) use a monitoring tool to look at your SQL Server performance (task
manager or performance

monitor will do) look at the memory useage and the CPU processor usage. If
your CPU is running at

90-100% for a long time then you may have a problem with #1 (bad queries or
stored procs) or you

may just need more processing power if you have a lot of users. If you SQL
Server is maxed on its

memory and is having to do a lot of paging, then you need more memory.
4) you may be experiencing some network congestion between your SQL Server
box and your

WebServer. Hopefully your servers are on the same network (I mean I hope you
are not trying to

connect to your SQL Server through the Internet, if you are then good luck,
you will always have

problems with the connection timing out). You may need to monitor the
network if you can't find

anything else wrong.

Reference :http://dotnetjunkies.com/Forums/ShowPost.aspx?PostID=9555

If all these things does not solve your problem then as a last option you
may consider increasing command time out value at application level in
following manner:
ADO.NET, there is a CommandTimeout property of the command object that
defaults to 30 seconds. A specification of 0 means infinite.You can also set
this value at application level by following code:
System.Data.IDbCommand.CommandTimeout = 50;

Hope this helps.
--
If my answer helped you,then please do press Yes below.
Thanks and Regards.
Manish Bafna.
MCP and MCTS.
 
A few corrections:

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Manish Bafna said:
What William (Bill) Vaughn has said is correct.You must first try to speed
up
the query.On one forum i found following

tips as to how to speed up query.

As far as the database connection timing out, if you have a lot of users
you
may need to increase

the size of the connection pool. I think that the default is 20, maybe you
need more.
Click to expand...

The command
 
William , Manish thanks a lot for your suggestions\ Answers :

Manish one of the suggestions that you have is to do the following
System.Data.IDbCommand.CommandTimeout = 50;

when i tried dong this it seems that
CommandTimeout is not a method of the IDbCommand at all, am i dong something
wrong or is that a different version of .Net framework?

thanks
 
Back
Top