Get the best performance from your ADO application
The word "performance" has several diverse and subtle implications. When people talk about how well something performs, one thing they might mean is how much work gets finished during a period of time. For example, a well-performing engine runs quietly and produces lots of torque. You can apply this same yardstick to your development team: A well-performing development team also works quietly and produces lots of good code. To me, performance means at least these two things—how well my code does its job and how well my team and I do our jobs. The tips in this article can help you do both—write code faster and write faster code—and do so quietly, with fewer noisy errors. Primarily, the tips pertain to ADO, especially how you use it to access SQL Server. But along the way, I touch on a few more general COM tips that apply to any Visual Basic (VB) code you write.
I've spent quite a bit of time working out which SQL Server data-access coding techniques, which architectures, and which development practices make for the best performance. In some cases, a technique has little bearing on the overall performance of the application or component unless you multiply its savings with repetition. For example, in a client/server application, you might save a second or two by not specifying an ODBC Data Source Name (DSN) for your connection. This savings might have little overall effect on the application's suitability or performance, but if you apply that same technique to a middle-tier component in which connections are made and broken several hundred (or thousand) times per minute, hour, or day, this technique can significantly affect how the system performs. For each technique I discuss, be sure to consider the multiplication factor—how many times your system executes this code during a period of time.
When beginning your search for ways to improve performance, consider where your application (component or Active Server Pages—ASP—code) spends most of its time waiting and processing. If you discover your application is spending a lot of time waiting for the Open or Execute method to complete, you need to take a hard look at your server-side query strategy. All data-access interfaces—including ADO—wait at the same speed for results. For example, if you have a query that takes SQL Server 20 seconds to execute, no matter which interface you use to execute the query, none will return with the result any faster than another. Although some interfaces open connections faster and some process the resultsets faster, none can affect the speed at which the engine compiles and executes a query. So, all the ADO techniques in the world might not help your performance if you're submitting "challenged" queries—for example, you haven't optimized your indexes, you haven't used stored procedures, the server is overloaded, or you're asking the application to return too many rows. No other tuning techniques will significantly help your overall performance until you solve these basic query problems. SQL Server's Query Analyzer provides a great way to analyze query performance. It can graphically display how the query is being executed and can suggest ways to improve performance.
After you're comfortable that your queries are efficient, you can use the techniques I discuss in this article to tune your ADO code even further. These tips will help you streamline the process of establishing and maintaining connections, build and submit queries that perform more quickly and efficiently, improve performance during result processing, and compare data-access strategies.
Establishing the Connection
In a client/server application, you have several ways you can hide the time it takes to establish an initial connection so that the user doesn't have to wait for the application to start while the connection is opened. First, try connecting asynchronously. With asynchronous connections, ADO starts the connection process but doesn't wait for it to complete before returning control to your application—thereby permitting your application to execute most of its initialization process and complete the form_load event more quickly. If you can close the connection and reconnect in less time than it takes for the connection pool to release your connection, your connection is virtually instantaneous. But in many cases (especially when you don't have many users), simply leaving the connection open makes the most sense. I recommend you leave the Connection object open in a middle-tier or ASP component if it makes repeated queries to the database.
Another way to improve connection performance is to avoid using ODBC with DSNs. ODBC is now in Quick Fix Engineering (QFE) mode at Microsoft, which means that the company will do no further work on ODBC or its drivers unless it discovers a major bug. ODBC DSNs are also a concern when you're managing performance and deployment. DSNs have to be installed on the client system, require a registry lookup, and can take longer to establish than OLE DB connections—especially if you hard-code the ConnectionString. In real terms, if you eliminate DSNs, your overhead savings will be minor. If you eliminate the connection altogether, you'll probably save 2 to 5 seconds per connection—assuming a connection isn't available from the pool. However, if your application has to constantly connect and reconnect, these savings can add up.
When establishing your connection, you choose the data provider. Microsoft recommends that you use OLE DB instead of the default ODBC provider. My experience shows fewer unpleasant surprises with the latest OLE DB native providers than with their older ODBC counterparts. In any case, be sure to test your application thoroughly before committing to a new provider—your code's performance, supported features, and behavior might vary.
In the middle tier and in ASP, you can't (in a practical sense) hold a connection open and still create a scalable component—at least not between invocations. Typically, a component or ASP page is loaded and discarded frequently as Microsoft IIS references and releases instances. Because the ADO-based code must establish, use, and release a connection each time the code is executed, strategies to minimize connection setup can help in clearly measurable terms. For these scenarios, the connection/session pool plays a big role in how quickly you can get connected. If you code your Command object's ConnectionString property correctly (i.e., by using the same server, initial catalog, login ID, and other parameters each time), the chances of a connection being open and available are good. If a matching connection is found in the pool, the time to connect (or reconnect) will be virtually nil (usually less than 250ms).
However, if your ADO (or VB) code doesn't release your Connection object or if you change the ConnectionString from instance to instance, OLE DB has to establish a new connection every time. If that happens, you'll soon run out of connections as the available pool dries up. To make sure the connection is freed, set the Connection object to Nothing after closing it. Also, don't use a ConnectionString in the Recordset Open method; open the Connection object independently so that the Connection object is easy to reference when you close it and set it to Nothing.
Building and Submitting a Query
The whys and why-nots of query construction is a complex area. However, some basic guidelines can help make constructing an efficient query a smoother process. Generally, use queries that don't waste the server's time. Here are several ways to build better, more efficient queries.
Don't force SQL Server to recompile and construct a query plan for your query each time it's executed. An easy way to avoid this repetition is to create and use parameter-based stored procedures. Don't bother with the ADO Command object's Prepare property—it doesn't work correctly. When creating stored procedures, you can further help ADO performance by eliminating unneeded "rows affected" values from the returned resultsets—simply add SET NOCOUNT ON to the stored procedure.
Make as few round-trips to the server as possible. If you have several related operations to perform, combine them into a stored procedure or even one multipart query that you can execute as a script on the server. Avoid using methods (such as Refresh) and improper Parameters collection references that force ADO to make unnecessary round-trips to the server.
In client/server applications, construct Command objects once, not each time they're used. Reset the Command parameter values, and re-execute the Commands as necessary.
Try to get ADO to generate a direct remote procedure call. Watch SQL Server Profiler to see when ADO asks SQL Server to run a procedure by leveraging the sp_executesql procedure. This procedure isn't particularly inefficient, but if you see SQL Server being asked to create a temporary procedure in one step, execute the procedure in another step, and drop the same procedure in a third step, you've hit a performance snag.
Whenever your query doesn't return a rowset, be sure to use the adExecuteNoRecords option to tell ADO to bypass any code required to set up and receive a rowset (in Recordset form). You can pass this option to the Execute method or as a Command option.
Don't use Command objects when you're executing simple rowset-returning stored procedures. All stored procedures (and Command objects) show up as COM methods of the Connection object. Having procedures as Connection methods can yield significant performance benefits as well as simpler code. Although this technique doesn't help with stored procedures that echo Return Status values or Output parameters, it can be helpful for action queries and other queries that return one or more rowsets. With the procedures as Connection methods, you can pass the stored procedure's input arguments as method arguments, and if a Recordset is returned, you can reference the Recordset as the last argument in the method call. For example, the following ADO statement executes a stored procedure called "Fred" that has two input parameters and a returned Recordset:
MyConnection.Fred "InputArg1", 2, myRecordset
When coding, don't expect VB to auto-complete the stored procedure or Command object name as a recognized method of the Connection object. COM doesn't resolve these until runtime.
Prev. page
[1]
2
3
next page 
