|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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, 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. Show quote "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, 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. -- Show quote____________________________________ 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,
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 Show quote "William (Bill) Vaughn" <billvaRemoveT***@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, >> >> > > 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. -- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- "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" <billvaRemoveT***@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. -- Show quoteMiha 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" <billvaRemoveT***@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, >>> >>> >> >> > 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. -- Show quote____________________________________ 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" <billvaRemoveT***@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 Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> schreef in bericht news:OzQ3iB3NHHA.1248@TK2MSFTNGP03.phx.gbl... > 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" <billvaRemoveT***@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, >>>>> >>>>> >>>> >>>> >>> >> > > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message It is true for WinForms also, as long as you want a responsive application.news:OzQ3iB3NHHA.1248@TK2MSFTNGP03.phx.gbl... > 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. 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/ 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 Show quote "Andrew Robinson" <nemoby@nospam.nospam> schreef in bericht 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,  |
|||||||||||||||||||||||
Other interesting topics