Bill,
This is an asp.net app so lets call it scenario 3.0 and I understand
that we want to tie up the thread pool for as short a period of time as
possible. But, if I am going to execute 2 or 3 "command.ExecuteQuery()"
methods (one after the next), are you still saying that we should open
and close between each of them?
I tend to say but not sure that is based on a performance requirement
but more just on a best practice standpoint. You never know when you
will get bumped out of the current thread pool and by slicing in as fine
grained a programming model as possible we are allowing the system to
perform at its best.
Pretty sure I know the answer here but will feel better knowing the king
of all things sql agrees.
Thanks again.
-Andy
And then there is Scenario 2.5:
Your company has a LAN and it has to support a thousand (typically
far fewer) active connections as applications come and go during the
day. In this case since SS can handle these connections with ease, it's
fine to open and keep a large number of connections open indefinitely.
And Scenario 3.0
Your company supports a web site which is running ASP applications.
In this case the way that the applications are instantiated and torn
down after they're used dictate that you open and quickly close
connections as they are used. In this case the connections are handled
by the Connection Pool which is used to hold a limited number of
connections open so the overhead of connecting is minimized.
--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------
It is all a matter of choice, but there a number of factors tht you
would need to consider when making that choice, for example:
Scenario 1:
Your application 'talks' to Sql Server (maybe Express) on the same
machine and no other application accesses that Sql Server at all.
In this case there is never going to be any contention for connection
resources and it would be quite safe to open the connection once and
leave it open.
Scenario 2:
Your application 'talks' to Sql Server on a networked server and that
application is installed and in continual use on 10000 other machines
on the networks all 'talking' to the same Sql Server.
In this case there is going to be contention for connection resources
and it would be probably be quite unsafe to open the connection once
and leave it open.
As you can see there are best case and worst case scenarios and there
will be a series of 'shades' in between.
As far as benchmarks are concerned, simply time how long it takes to
connect the first time and then time how long it takes to connect
subsequently, Because of connection pooling and other factors I think
you will find that there is little significant overhead in this
respect but the time it takes to connect is only one factor.
I recently got into a discussion with a coworker about just how long
to leave a connection open. I have always opened as late as I can and
closed at the earliest possible point in time.
using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand(InsertCommandText, cn))
{
cm.CommandType = InsertCommandType;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
}
Is there any reason other than executing within a transaction to
leave a connection open? What if I am executing a series of commands
outside of a transaction? Can anyone point me to any benchmarks on
this?
Thanks,
