 | |
Best connection management
Dotnet Framework (ADO.net) microsoft.public.dotnet.framework.adonet
 |
| | Thread Tools | Search this Thread | Display Modes |
#11
 
| |||
| |||
|
Re: Best connection management -
09-13-2007
, 10:37 AM
|
You don't need try/finally just for making sure that connection is closed as this is enough (Dispose will call Close for you): using (SqlConnection conn = new SqlConnection(...)) { try { ...} ... } |
#12
| |||
| |||
|
|
"William Vaughn" <billvaNoSPAM (AT) betav (DOT) com> wrote in message news:urZ4xKO9HHA.2004 (AT) TK2MSFTNGP06 (DOT) phx.gbl... As I have said many times before, this JIT connection strategy is a "best practice" for ASP architectures. It is not always (or even usually) the best choice for "connected" Windows Forms architectures. JIT precludes use of server-side state management which can dramatically improve query performance. Consider that each trip to the ConnectionPool requires the interface to reauthenticate your credentials and reset the connection--these operations are not free and totally unneeded with client/server architectures. Sorry but this statement doesn't hold water. This is the best practice for winforms client/server apps, too. 1. There are usually not much separate db operations at all and thus visiting the connection pool once in a while is not a performance hit. OK, you can start nickpicking that you loose a millisecond in a day. 2. DB operation time compared to "performance hit" is so huge that "perf hit" might account for 0.00000000000000000000000000000000000001%. 3. When you do multithreading (which I assume you aren't) connection pool comes in as a great time saver. 4. It is easier to handle connection failures. If it fails due to network issue (or some other failure unrelated to logic), just (optionally clear the pool) repeat the operation, no additional code is required. I am sure there are other benefits as well. -- 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/ |
#13
| |||
| |||
|
|
Ah no. The JIT approach derails (makes impossible) any number of data access strategies that can dramatically improve performance. I know, as I've used them to decrease query time by 90%. Since the application manages server-side objects like TempDB lookup tables (used in subsequent query JOINs), or server-side cursors where I didn't need to use disconnected data architectures, I was able to write far more efficient and suitable applications to support more users more quickly with less hardware. Yes, you can still build server-side cursors using ADO.NET (ANSI cursors) and no, they are not suitable for all applications. But consider that the vast majority of Windows Forms applications have to support 20-200 users, server-side cursors, use of TempDB table index products and other techniques that require a persistent connection make sense. Microsoft had apps that supported over 1000 users on a 386/33 using these techniques.And yes, these applications use multi-threading and in this case I simply open another connection. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva 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" <miha at rthand com> wrote in message news:%23nsCnwd9HHA.3900 (AT) TK2MSFTNGP02 (DOT) phx.gbl... "William Vaughn" <billvaNoSPAM (AT) betav (DOT) com> wrote in message news:urZ4xKO9HHA.2004 (AT) TK2MSFTNGP06 (DOT) phx.gbl... As I have said many times before, this JIT connection strategy is a "best practice" for ASP architectures. It is not always (or even usually) the best choice for "connected" Windows Forms architectures. JIT precludes use of server-side state management which can dramatically improve query performance. Consider that each trip to the ConnectionPool requires the interface to reauthenticate your credentials and reset the connection--these operations are not free and totally unneeded with client/server architectures. Sorry but this statement doesn't hold water. This is the best practice for winforms client/server apps, too. 1. There are usually not much separate db operations at all and thus visiting the connection pool once in a while is not a performance hit. OK, you can start nickpicking that you loose a millisecond in a day. 2. DB operation time compared to "performance hit" is so huge that "perf hit" might account for 0.00000000000000000000000000000000000001%. 3. When you do multithreading (which I assume you aren't) connection pool comes in as a great time saver. 4. It is easier to handle connection failures. If it fails due to network issue (or some other failure unrelated to logic), just (optionally clear the pool) repeat the operation, no additional code is required. I am sure there are other benefits as well. -- 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/ |
#14
| |||
| |||
|
|
Bill, Aren't you in effect saying that the vast majority of Windows Forms applications would be better off using classic ADO than ADO.Net? Kerry Moorman "William Vaughn" wrote: Ah no. The JIT approach derails (makes impossible) any number of data access strategies that can dramatically improve performance. I know, as I've used them to decrease query time by 90%. Since the application manages server-side objects like TempDB lookup tables (used in subsequent query JOINs), or server-side cursors where I didn't need to use disconnected data architectures, I was able to write far more efficient and suitable applications to support more users more quickly with less hardware. Yes, you can still build server-side cursors using ADO.NET (ANSI cursors) and no, they are not suitable for all applications. But consider that the vast majority of Windows Forms applications have to support 20-200 users, server-side cursors, use of TempDB table index products and other techniques that require a persistent connection make sense. Microsoft had apps that supported over 1000 users on a 386/33 using these techniques.And yes, these applications use multi-threading and in this case I simply open another connection. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant, Dad, Grandpa Microsoft MVP INETA Speaker www.betav.com www.betav.com/blog/billva 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" <miha at rthand com> wrote in message news:%23nsCnwd9HHA.3900 (AT) TK2MSFTNGP02 (DOT) phx.gbl... "William Vaughn" <billvaNoSPAM (AT) betav (DOT) com> wrote in message news:urZ4xKO9HHA.2004 (AT) TK2MSFTNGP06 (DOT) phx.gbl... As I have said many times before, this JIT connection strategy is a "best practice" for ASP architectures. It is not always (or even usually) the best choice for "connected" Windows Forms architectures. JIT precludes use of server-side state management which can dramatically improve query performance. Consider that each trip to the ConnectionPool requires the interface to reauthenticate your credentials and reset the connection--these operations are not free and totally unneeded with client/server architectures. Sorry but this statement doesn't hold water. This is the best practice for winforms client/server apps, too. 1. There are usually not much separate db operations at all and thus visiting the connection pool once in a while is not a performance hit. OK, you can start nickpicking that you loose a millisecond in a day. 2. DB operation time compared to "performance hit" is so huge that "perf hit" might account for 0.00000000000000000000000000000000000001%. 3. When you do multithreading (which I assume you aren't) connection pool comes in as a great time saver. 4. It is easier to handle connection failures. If it fails due to network issue (or some other failure unrelated to logic), just (optionally clear the pool) repeat the operation, no additional code is required. I am sure there are other benefits as well. -- 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/ |
|
« Previous Thread
|
Next Thread »
| Thread Tools | Search this Thread |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.