USEFUL SITES :
Write for Us
Performance Tuning Tips for Creating Visual Basic Applications Using SQL Server
While ADO, ADO.NET (and other VB object libraries) make database manipulation easy for the programmer, using these shortcuts can kill SQL Server performance. As a rule of thumb, encapsulate your DML (Data Manipulation Language) in stored procedures and run them from your VB application. This bypasses object library overhead (such as reducing cursors) and reduces the chatter between the VB application-based and SQL Server over the network.
So what does this mean in practice? Essentially, avoid using the recordset object to SELECT and modify (INSERT, UPDATE, DELETE) data in your VB code. Instead, use Transact-SQL, encapsulated in stored procedures, to SELECT and modify data in a SQL Server database. An ADO or ADO.NET recordset should be used as a method of reading data, not modifying data. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
*****
When using an ADO or ADO.NET recordset to return data from SQL Server, the most efficient way is to use what is often called a firehose cursor. The firehouse cursor is really an incorrect term because it is not a cursor. A firehose cursor is just a method to quickly move data from SQL Server to the client that requested it.
Essentially, a firehose cursor sends the requested data (from the query) to an output buffer on SQL Server. Once the output buffer is full, it waits until the client can retrieve the data from the output buffer. Then the output buffer is filled again. This process repeats over and over until all of the data is sent to the client. Another advantage of this method is that records are only locked long enough to be moved to the output buffer, helping to boost concurrency and performance.
When you open an ADO or ADO.NET RecordSet and use its default settings, a firehose cursor is automatically used by default. If you want to specify a firehouse cursor manually, you can do so by using these property settings:
When the client receives the data from the firehose cursor, the data should be read into a local data structure for local use by the client. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
When accessing data on a SQL Server, write your VB code so as to minimize the number of round-trips between the application and SQL Server. Each and every time you use ADO to execute Transact-SQL code to get data from SQL Server, multiple, time-consuming steps have to occur. For example:
If you know much about the technical details of networking, then you know that the above steps have been oversimplified. The point to remember is that round-trips between your application and SQL Server are expensive in time and resources, and you need to do your best in your code to minimize them, in order to maximize application performance. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
One way to help reduce round-trips between your application and SQL Server is to move the data you need at the client from SQL Server in a single query, not in multiple queries. I have seen some applications that only retrieve one row at a time, making a round-trip for every row needed by the application. This can be very expensive in resources and it hurts performance.
Of course, you can't always know what rows will be needed ahead of time, but the better you can guess, even if you guess and return too many rows, returning them in one round-trip is often more efficient than retrieving only one row at a time. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
While this may seem like ancient history, don't use DAO to access SQL Server, it is performance suicide. Also avoid ODBCDirect. Instead, use RDO, ADO, and ADO.NET, with ADO.NET being the current preference. You would be surprised at how many SQL Server-based applications that are still around using this old and poorly-performing technology. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
When creating a connection using ADO or ADO.NET, be sure you use the OLE DB provider, not the older ODBC provider for SQL Server, or the ODBC provider for OLE DB. The parameter you will use in your connection string is "provider=sqloledb". The OLE DB provider performs much more efficiently than the ODBC provider, providing better performance. You may not notice the speed boost if your application makes a single connection and the connection always stays open. But if your applications opens and closes hundreds or thousands of connections, then the benefit will become apparent. [7.0, 2000, 2005] Updated 7-15-2004
If you are VB developer and need to access SQL Server data, but don't have the time or interest in learning how to write stored procedures, consider using the GetRows method of the RecordSet object. The GetRows method is used to pull all the records from the recordset into an array, which is much faster than using embedded Transact-SQL to download a RecordSet to your application. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
If possible in your application, use stored procedures to "batch" a set of related Transact-SQL statements together, instead of calling a separate stored procedure for every database task you want to perform. This helps to reduce network traffic and server overhead. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
If you have a related group, or batch, or Transact-SQL statements you want to execute, but you don't want to use a stored procedure, as generally recommended for dealing with batches of Transact-SQL statements, one option you can use to boost performance in your VB code is to concatenate two or more separate Transact-SQL statements into a single batch and execute them as a single message. This is much more efficient that sending the Transact-SQL code to SQL Server as multiple messages. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
When SELECTing data from your application to be returned to it from SQL Server, limit the amount of rows returned to only those that are needed now. If necessary, force the user to enter selection criteria to limit the results set. [6.5, 7.0, 2000, 2005]
If your application allows users to run queries, but you are unable in your application to easily prevent users from returning hundreds, even thousands of unnecessary rows of data they don't need, consider using the TOP operator within the query. This way, you can limit how may rows are returned, even if the user doesn't enter any criteria to help reduce the number or rows returned to the client. [6.5, 7.0, 2000, 2005]
If your application needs to perform looping, try to put the loop inside a stored procedure so it can be executed on the server without having to make round trips between the client and server, which hurts performance. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
When creating transactions in your application, don't create them using ADO's or ADO.NET’smethods. Instead, encapsulate the transaction in stored procedure so that it executes on the server, which reduces round-trips, helping to boost performance. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
If you have the need to filter or sort data on-the-fly at the client, let ADO or ADO.NET do this for you at the client. When the data is first requested by the client from the server (ideally using a stored procedure), have all the data the client wants to "play" with sent to the client. Once the recordset is at the client, then ADO methods can be used to filter or sort the data. This helps to reduce network traffic and takes some of the load off of the server. [6.5, 7.0, 2000, 2005] Updated 11-28-2003
By default, the CacheSize property of the ADO Recordset object determines how many rows are fetched from a server-side cursor at a time. The default is one. This means each row of the recordset is returned one at a time from the server to the client. This is very inefficient.
The CacheSize property needs to be set to a much higher figure, such as between 100 and 500, depending on the number of rows that are to be eventually returned from the server to the client.
You can use Profiler to capture the communication between your VB application and SQL Server, and if you see only one record being returned at a time via a cursor, you know you are in trouble. [6.5, 7.0, 2000] Updated 7-15-2004
Next Page>>
