| Groups | Blog | Home |
dotnet ado.net : leave connections open for how long?
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,
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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Stephany Young" <noone@localhost> wrote in message news:e11kDoeNHHA.5000@TK2MSFTNGP03.phx.gbl... > 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. > > > "Andrew Robinson" <nemoby@nospam.nospam> wrote in message > news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >>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, > >
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 [quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:etBt8FfNHHA.4916@TK2MSFTNGP06.phx.gbl... > 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) > ----------------------------------------------------------------------------------------------------------------------- > > "Stephany Young" <noone@localhost> wrote in message > news:e11kDoeNHHA.5000@TK2MSFTNGP03.phx.gbl... >> 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. >> >> >> "Andrew Robinson" <nemoby@nospam.nospam> wrote in message >> news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >>>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, >> >> > >
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. [quoted text, click to view] "Andrew Robinson" <nemoby@nospam.nospam> wrote in message news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >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,
Not at all (while some would say you should). If your code is well written
and can't leak connections (falling into an exception handler and not closing) then executing several methods on the same connection is fine--and IMHO should yield better performance. Opening connections is not free--even from the Connection Pool. If there is a chance that you'll get bumped out, the only way might be to wrap the connections in a Using scope. However, I doubt if any swapping would be permitted to break into an active connection operation. If it did, we would have a lot more overflowing pools than we have nowadays. -- ____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Andrew Robinson" <nemoby@nospam.nospam> wrote in message news:Omn6GgpNHHA.4244@TK2MSFTNGP04.phx.gbl... > 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 > > "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message > news:etBt8FfNHHA.4916@TK2MSFTNGP06.phx.gbl... >> 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) >> ----------------------------------------------------------------------------------------------------------------------- >> >> "Stephany Young" <noone@localhost> wrote in message >> news:e11kDoeNHHA.5000@TK2MSFTNGP03.phx.gbl... >>> 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. >>> >>> >>> "Andrew Robinson" <nemoby@nospam.nospam> wrote in message >>> news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >>>>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, >>> >>> >> >> >
Andrew,
I don't agree with the previous writers. As you can avoid to create a program for a special scale, than avoid it. As it has no big consequences for the real performance, than build your application for a large scale. If you are using one connection in a program, than you can always rescale it, but it will be a lot of work, that is needles done if you had in mind direct an environment from that scale. This is of course not if you have to do much additions for your code. But that is in my idea never. By instance a dataadapter will automatically open and close a connection for you, if you have not closed before. Have you any idea why that was done already as a standard? Cor "Andrew Robinson" <nemoby@nospam.nospam> schreef in bericht news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... [quoted text, click to view] >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,
Again, this is true for ASP--not necessarily for connected Windows Forms
applications and not at all for SQLCe applications or other single-user DBMS engine applications. -- ____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:4656A36E-2352-47DF-98D4-4C870D040B5F@microsoft.com... > The definition is open as late as possible and close it as soon as > possible. That doesn't imply that you should open and close connection for > each command. It rather implies that you have to open the connection right > before doing database operations and close it asap you finished with > database operation*s*. > Opening and closing for each operation doesn't make sense. > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "Andrew Robinson" <nemoby@nospam.nospam> wrote in message > news:Omn6GgpNHHA.4244@TK2MSFTNGP04.phx.gbl... >> 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 >> >> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message >> news:etBt8FfNHHA.4916@TK2MSFTNGP06.phx.gbl... >>> 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) >>> ----------------------------------------------------------------------------------------------------------------------- >>> >>> "Stephany Young" <noone@localhost> wrote in message >>> news:e11kDoeNHHA.5000@TK2MSFTNGP03.phx.gbl... >>>> 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. >>>> >>>> >>>> "Andrew Robinson" <nemoby@nospam.nospam> wrote in message >>>> news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >>>>>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, >>>> >>>> >>> >>> >> >
The definition is open as late as possible and close it as soon as possible.
That doesn't imply that you should open and close connection for each command. It rather implies that you have to open the connection right before doing database operations and close it asap you finished with database operation*s*. Opening and closing for each operation doesn't make sense. -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ [quoted text, click to view] "Andrew Robinson" <nemoby@nospam.nospam> wrote in message
news:Omn6GgpNHHA.4244@TK2MSFTNGP04.phx.gbl... > 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 > > "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message > news:etBt8FfNHHA.4916@TK2MSFTNGP06.phx.gbl... >> 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) >> ----------------------------------------------------------------------------------------------------------------------- >> >> "Stephany Young" <noone@localhost> wrote in message >> news:e11kDoeNHHA.5000@TK2MSFTNGP03.phx.gbl... >>> 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. >>> >>> >>> "Andrew Robinson" <nemoby@nospam.nospam> wrote in message >>> news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >>>>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, >>> >>> >> >> >
Bill,
Beside technical reasons are there as well "maintenance reasons". Those are for me always much more important than any technical benefit. (They seldom confict in a way that it takes seconds). The open and close in the way as Miha describes is one of those I prefer because of the sentence above. Just my opinion. Cor "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> schreef in bericht news:OzQ3iB3NHHA.1248@TK2MSFTNGP03.phx.gbl... [quoted text, click to view] > Again, this is true for ASP--not necessarily for connected Windows Forms
> applications and not at all for SQLCe applications or other single-user > DBMS engine applications. > > -- > ____________________________________ > 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) > ----------------------------------------------------------------------------------------------------------------------- > > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > news:4656A36E-2352-47DF-98D4-4C870D040B5F@microsoft.com... >> The definition is open as late as possible and close it as soon as >> possible. That doesn't imply that you should open and close connection >> for each command. It rather implies that you have to open the connection >> right before doing database operations and close it asap you finished >> with database operation*s*. >> Opening and closing for each operation doesn't make sense. >> -- >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> RightHand .NET consulting & development www.rthand.com >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> "Andrew Robinson" <nemoby@nospam.nospam> wrote in message >> news:Omn6GgpNHHA.4244@TK2MSFTNGP04.phx.gbl... >>> 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 >>> >>> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message >>> news:etBt8FfNHHA.4916@TK2MSFTNGP06.phx.gbl... >>>> 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) >>>> ----------------------------------------------------------------------------------------------------------------------- >>>> >>>> "Stephany Young" <noone@localhost> wrote in message >>>> news:e11kDoeNHHA.5000@TK2MSFTNGP03.phx.gbl... >>>>> 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. >>>>> >>>>> >>>>> "Andrew Robinson" <nemoby@nospam.nospam> wrote in message >>>>> news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >>>>>>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(); >>>>>> } >>>>>>
[quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message news:OzQ3iB3NHHA.1248@TK2MSFTNGP03.phx.gbl... > Again, this is true for ASP--not necessarily for connected Windows Forms > applications It is true for WinForms also, as long as you want a responsive application. and not at all for SQLCe applications or other single-user DBMS [quoted text, click to view] > engine applications. I tend to disagree. It is true when you are multithreading - not sure on how SQLCe fits in this scenario - does it allow multiple connections? I guess yes. Anyway I was commenting on the definition open late, close early and not that this rule applies to all situations. -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Thanks for all the info. As with most things in the programming world, it
appears that there are a number of opinions on how this should be done. It feels good to know that even though there is no single correct answer, I am within the spread. -Andy [quoted text, click to view] "Andrew Robinson" <nemoby@nospam.nospam> wrote in message
news:%23sAcGSdNHHA.4928@TK2MSFTNGP06.phx.gbl... >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,
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |