When calling SQL Server stored procedures from the ADO Command object, don’t use the Refresh method to identify the parameters of a stored procedure. This produces extra network traffic, slowing performance. Instead, explicitly create the parameters yourself using ADO code. [7.0, 2000, 2005] Updated 7-15-2004
*****
ADO allows you to create four different types of SQL Server cursors. Each has its own place, and you will want to choose the cursor that uses the least possible resources for the task at hand. When at all possible, attempt to use the Forward-Only cursor, which uses the least amount of overhead of the four cursor types. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
Avoid using the MoveFirst method of the RecordSet object when using a Forward-Only cursor. In effect, when you use this method, it re-executes the entire query and repopulates the Forward-Only cursor, increasing server overhead and killing performance. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
If you create COM objects to encapsulate database access, try to follow these two suggestions if you want optimum speed: 1) use in-process dlls; and 2) use early-binding. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
Consider using ADO’s ability to create disconnected recordsets to help reduce the load on SQL Server. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When storing your SQL Server data into VB variables, always use strongly typed variables. Avoid using the variant data type (which is not always possible), as it has greater overhead than the other data types. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
If you create object variables in your VB code to refer to COM objects that hold SQL Server data, be sure to strongly type them. Avoid using the AS OBJECT keywords, instead, always explicitly specify the type of object you want to create. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When instantiating COM objects to hold SQL Server data, create them explicitly, not implicitly. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
If you will be calling the same stored procedure, view, or SQL statements over and over again in your code, don’t create a new Command object each time. Instead, reuse the Command object. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When looping through recordsets, be sure you bind columns to field objects before the looping begins. Don’t use the Fields collection of the Recordset object to assign values for fields in a Recordset within each loop, as it incurs much more overhead. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
If you know that the results of a query from within a stored procedure you call will return only one row of data (and not an entire recordset), don’t open an ADO Recordset for the purpose of retrieving the data. Instead, use a stored procedure output parameter for the best performance. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
If your application needs to insert a large binary value into an image data column, perform this task using a stored procedure, not using an INSERT statement embedded in your application. The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time). Using a stored procedure avoids all this. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When ADO is used to open more than one ForwardOnly recordset on a single Connection object at a time, only the first recordset is opened using the Connection object you previously created. Additional new connections don’t use the same Connection object. Instead, separate connections are created for each ForwardOnly recordset you create after the first. This occurs because SQL Server can only open one ForwardOnly cursor per connection. The more connections you create, the greater the stress on SQL Server, and performance and scalability suffer.
To avoid this problem, don’t use a ForwardOnly recordset. Static, Keyset, and Dynamic recordsets don’t have this problem. Another option is to use a client side cursor instead of SQL Server cursor. Or you can close each recordset before opening another on the same connection. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When making your connection to SQL Server, choose DSN-less connections for the fastest connection. Not only does it make database connections faster, it allows you to use the OLE DB provider, which is not available using a DSN-based connection. The OLE DB provider is the fastest provider you can use to access SQL Server.
If you do need to use a DSN, select System DSNs over File DSNs, because they are faster when making connections. [7.0, 2000, 2005] Updated 7-15-2004
*****
When creating a Connection object, always create it explicitly, not implicitly. Opening a Connection object explicitly consumes less resources than opening it implicitly, and it also allows you to more efficiently manage multiple connections and to reassign the various roles that the Connections objects perform within your application. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When using recordsets, be sure to open them explicitly, not implicitly. When recordsets are opened implicitly, you cannot control the default cursor and lock types, which are, respectively, forward-only and read-only. If you always open your recordsets explicitly, then you can specify which cursor and lock types you want to invoke for this particular situation, specifying the types with the least amount of overhead to accomplish the task at hand. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
When using ADO or ADO.NET to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of “connection creep”. Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server’s performance. [6.5, 7.0, 2000, 2005] Updated 7-15-2004
*****
If you are connecting to SQL Server via either OLE DB (version 2.0 or higher) or ODBC (version 3.0 or higher), SQL Server connection pooling is automatically implemented for you. Because of this, you don’t have to write special code to implement connection pooling yourself. In addition, you don’t want to even reuse an ADO connection object, which is commonly done by many VB developers.
If you want to take the best advantage of database connection pooling, and optimize your VB application’s SQL Server data access, the best advice you can receive is to be sure that you only open a database connection just before you need it, and then close it immediately after you are done with it. Don’t leave database connections open if you are not using them.
When you create or tear down a database connection in your VB code, you aren’t really creating a new connection or tearing down a current connection. What is happening is that your connection requests are sent to OLE DB or ODBC, and they determine if a connection needs to be created or torn down. If a new connection is needed, then one is created, or one is used from the current connection pool. And if you request that a connection be torn down, it will actually pool the unused connection until it is needed, or tear it down if it is not reused within a given time period. [6.5, 7.0, 2000, 2005] Updated 12-6-2004
*****
In order for connection pooling to work correctly, be sure each connection you open uses the same ConnectionString parameters. Connection pooling only works if all of the parameters for the ConnectionString are identical. If they are all not identical, then a new connection will be opened, circumventing connection pooling. [6.5, 7.0, 2000, 2005] Updated 12-6-2004
*****
If appropriate for your application, locate the application’s data access components on the SQL Server where the data is, instead of at the client. This can significantly reduce network traffic and overhead and boost data throughput. [6.5, 7.0, 2000, 2005] Updated 12-6-2004
*****
When creating a Command object to execute a stored procedure against SQL Server, you can use either the adCmdText or the adCmdStoredProc property to tell ADO that you want to execute a stored procedure. Always use the adCmdStoredProc property, which uses RPCs between the client and SQL Server. This acts to bypass parameter translation and boosts performance from 20 to 30 percent over using the adCmdText property. [6.5, 7.0, 2000, 2005] Updated 12-6-2004
*****
If you need to execute a stored procedure from a Command object, and if the stored procedure will not return any rows, you can boost performance of the Command object by setting the adExecuteNoRecords option. This tells the Command object to not ask for a returning rowset, which saves a little overhead and reduce memory usage. [6.5, 7.0, 2000, 2005] Updated 12-6-2004
*****
If you need your VB application to generate a unique value for use in a primary key column in a SQL Server table, performance will be slightly better if you let SQL Server, instead of your VB application, create the unique value. SQL Server can generate unique keys using either an Identity (using the Integer data type) column or by using the NEWID function in a UniqueIdentifier column. Of these two, Identify columns offer better performance. [6.5, 7.0, 2000, 2005] Updated 12-6-2004
*****
When creating COM components to access SQL Server, try to design the component to have as few properties as possible. For example, instead of having a property for every column of data you want to send back or forth between the database and your application, create one generic property that can be used to send all of the columns at one time. What this does is reduce the number of calls that must be made by the component, reducing overhead on the component and SQL Server. [6.5, 7.0, 2000, 2005] Updated 12-6-2004