 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / April 2008ado connection object in vb6
I am working through some performance issues with regards to Insert and Update procedures using a global connection object. During the first lifecycle of my project, I used an activex dll to store all my classes and within each class they use this global connection object to interface with the database (SQL Server). On the most recent update I needed to use the global connection object within my exe project so I just created a second global connection object to the same DSN as the activex dll. So I basically have two, one for all the classes within the dll and one for all the other "needs" within the exe. Both are created within modules at the time the program launches. The end result, the application slows way down when doing any type of insert or update. Is there a limit on the number of connection objects? Should the objects be global or local variables? wb "wb" <none>'s wild thoughts were released on Tue, 22 Apr 2008 08:26:13 -0700 bearing the following fruit: >I am working through some performance issues with regards to Insert and >Update procedures using a global connection object. During the first [quoted text clipped - 10 lines] >or update. Is there a limit on the number of connection objects? Should >the objects be global or local variables? Global connection objects are bad in so many ways. I guess you're not even using transactions right? I'm not sure why (perhaps someone can fill in here) but I have seen the same performance issue in apps the use global connections and then increase the number of connections. Really you should create a connection at the point you use it and close it as soon as you are done. -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde "Using Transactions"? What do you mean? Should I be? Thanks, I will re-work the code so each object is locally created and destroyed (set to nothing) WB > "wb" <none>'s wild thoughts were released on Tue, 22 Apr > 2008 08:26:13 -0700 bearing the following fruit: [quoted text clipped - 29 lines] > > https://mvp.support.microsoft.com/profile/Jan.Hyde "wb" <none>'s wild thoughts were released on Tue, 22 Apr 2008 09:25:34 -0700 bearing the following fruit: >"Using Transactions"? What do you mean? Should I be? If you are doing any inserting, deleting, or updating the yea. J >Thanks, I will re-work the code so each object is locally created and >destroyed (set to nothing) [quoted text clipped - 34 lines] >> >> https://mvp.support.microsoft.com/profile/Jan.Hyde -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde > I am working through some performance issues with regards to Insert and > Update procedures using a global connection object. During the first [quoted text clipped - 12 lines] > > wb There is little wrong with a 'global' connection object in a VB6 application. In fact it is preferred over the over-head of continuously creating and destroying an ADODB.Connection object. ADO uses Connection Pooling. Opening and closing a Connection object just before use is a useful practice as it helps the pool to be more efficient. I doubt that creating two Connection objects is an issue in this case, since both would share the same connection pool. It wouldn't hurt to try and just use one. Pass the Exe's to the Dll. Again, doubt you would see much change. But continuously creating and destroying a Connection object will decrease performance. You mentioned a 'DSN'. Does this mean you are using an ODBC driver, or OLE for OLEDB? If you are, then immediately change your connection to use an OLE DB provider. Depending on SQLServer version you may want to use the native provider. Likely not a complete cure, but should help. There is a performance issue with SQLServer in that it can only open one for ward-only server-side cursor per connection at one time. So multiple requests can 'stack-up' so to speak. Not sure how this might relate your problem. You might piddle around with different cursor types, etc. for separate commands. You might be having locking issues. Migrating to a MTS model is only useful if you are in fact using Transactions. But then how are you doing your inserts and updates? Some techniques are more efficient than others. Just adopting more stored procedures is likely to add a magnitude of improvement. We need more details. -ralph Ok, now I am confused. One person recommends local objects, you recommend global objects..... The basic model that I have used inside the dll is that when I need to access the database I create a string variable and write the SQL. Then I use a local adodb.recordset object (lets call it objRS) and I code a line objRS.Open ("SQL string", global ado.connection). I populate the record data into the object I am dealing with, say a customer, and then I close the recordset and set it to nothing WB >> I am working through some performance issues with regards to Insert and >> Update procedures using a global connection object. During the first [quoted text clipped - 57 lines] > > -ralph "wb" <none>'s wild thoughts were released on Thu, 24 Apr 2008 21:43:18 -0700 bearing the following fruit: >Ok, now I am confused. One person recommends local objects, you recommend >global objects..... If you use global connections then you can never be sure whay state it is going to be in, and it may cause problems when using certain cursor types etc when you have more than one recordset open. Also, once you look at transactions then you'll see a global connection would be a poor choice. J >The basic model that I have used inside the dll is that when I need to >access the database I create a string variable and write the SQL. Then I [quoted text clipped - 67 lines] >> >> -ralph -- Jan Hyde https://mvp.support.microsoft.com/profile/Jan.Hyde > "wb" <none>'s wild thoughts were released on Thu, 24 Apr > 2008 21:43:18 -0700 bearing the following fruit: [quoted text clipped - 9 lines] > > J I think we must be talking about two different issues here. An ADO Connection Object when instanced creates the object and then sets up a connection pool. Then the object connects to the server authenticating the information and returns a valid connection to the pool. This is very expensive. It is best to keep the creating and destroying of Connection objects to a minium. That is why ADO was designed with Connection Pooling. Subsequent requests for a connection provide one from the pool which is magnitudes faster than creating one from scratch. So for the average desktop application creating one 'global' ADO Connection Object and re-using it, by opening and closing it, is the better strategy. Pooled connections are keyed against the data source, user name, password, provider string, etc. (Cursor, Locks, etc are NOT included.) So actually if a Desktop app creates Connection Object and later creates another within the same address space with the same connection string - it will get the same pool. One is only adding to the over-head by creating another reference. However, this is useful for Transactions since the Connection objects are actually maintained within the COM+ environment, and obviously a separate reference is needed for component, but you are actually using a 'global' Connection object there as well. -ralph > Ok, now I am confused. One person recommends local objects, you recommend > global objects..... [quoted text clipped - 8 lines] > > WB Lol! I'm confused as well. Jan's reply started out with ... "Global connection objects are bad in so many ways. I guess you're not even using transactions right?" This was so incorrect (from a person I known to be knowledgeable) I assumed he miss-understood your question. <g> There is often a confusion between a "Connection to a Database" and a "ADO Connection Object". The key is that ADO uses 'connection pooling'. It is actually working with the pool that determines the best strategy for your problem domain. (For example, some articles refer to pooling in an ASP application and others to a Desktop application, others to a distributed transactional environment. Each has subtle nuances in working with the ADO Pool. See if these help to remove the fog. "A Dip in the Pool" http://msdn2.microsoft.com/en-us/library/aa260761.aspx "SQL Server Connection Pooling Myths" By : Shawn Kresal http://www.sql-server-performance.com/article_print.aspx?id=224&type=art "PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled" http://support.microsoft.com/?kbid=271128 "Pooling in the Microsoft Data Access Components" http://msdn2.microsoft.com/en-us/library/ms810829.aspx "Support WebCast: ODBC Connection Pooling and OLEDB Session Pooling in Microsoft Data Access Components" http://support.microsoft.com/default.aspx?scid=kb;en-us;324686 -ralph > Ok, now I am confused. One person recommends local objects, you recommend > global objects..... [quoted text clipped - 8 lines] > > WB While the information on connection objects and connection pooling is useful it doesn't answer your specific question. So let me revisit it ... In this context ... Main Executable Creates a ADODB.Connection Uses a Dll Which creates an ADODB.Connection object Should NOT have much effect on performance, especially if you are using the same Connection general parameters. (Data Source, authentication, Properties, etc.) Your performance issues are likely due to something else. This is the normal architecture for your average client/server application: ' So global spot Public WithEvents adoCnn As ADODB.Connection ' ' Some init, load, main etc... On Error Goto ... ' create the object Set adoCnn = New ADODB.Connection ' open it creating your first authenticated connection ' test to make sure it valid etc. adoCnn.Open( .... ) ' note: Close doesn't really do all that much, it just releases the connection ' back to the pool adoCnn.Close ... Elsewhere in your application where you go to use it... Private Sub Command1_Click() ' setup any different cursors, locks you want etc. adoCnn.PropertyX = adSomething adoCnn.Open() ' or adoCnn.Open( , adSomething, etc) 'or if using the same, just open it adoCnn.Open 'Use the connection dim rs as adodb.recordset: Set rs = adoCnn.Execute() ... rs.close: set rs = nothing ' When done close it adoCnn.Close() End Sub Redo your application along these lines. This will remove any "Connection Issues" you might be having. Then isolate those areas where you are experiences performance issues and post back. People here can help with those specific situation. -ralph Wow, thank you for taking the time and giving detailed explanations. That helps me. Your architecture is exactly how I have designed my application. Up until the latest release where I added the connection within the exe, there weren't many issues. Since users have been complaining and I have verified slowdowns, it seemed like the best place to start was looking at whether or not two ado connections objects would be the cause. You seem to think two objects shouldn't matter, so there must be something else. Needle meet haystack.... WB >> Ok, now I am confused. One person recommends local objects, you >> recommend [quoted text clipped - 70 lines] > > -ralph Ah, Jan might not have read my book(s) on ADO and managing connections. There is nothing wrong with using a single connection per application. As a matter of fact, unless this is a web site, you'll get (slightly) better performance and far more flexibility as to the application design alternatives. However, using ODBC is not such a great idea. While there are far better approaches in use today (the SqlClient .NET native provider) OLE DB is the preferred interface for VB6 and ADO classic. I would also look again at the application design if I was doing a code review. When someone says "performance" and "INSERT" in the same sentence I wonder if the application is trying to do something it shouldn't. Consider that ADO classic was never designed to act as a bulk operations interface (it's a query interface). If you're moving (inserting) a lot of rows, I suggest an SSIS or bulk copy approach. If you're doing a lot of synchronization work, I suggest a replication approach. VB.NET supports both of these in ways that are dramatically simpler than ever before. Even without VB.NET you can still implement these more sophisticated technologies to let SQL Server do what it knows how to do without reinventing the wheel. hth  Signature__________________________________________________________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) ____________________________________________________________________________________________ > Ok, now I am confused. One person recommends local objects, you recommend > global objects..... [quoted text clipped - 77 lines] >> >> -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.