Performance Tuning Tips for Creating Visual Basic Applications Using SQL Server

When setting Connection Object properties, use the following dot notation instead of using the fully qualified object property notation, whenever appropriate, as it is faster.

Use this format for optimum speed:

WITH cn
     .ConnectionTimeout = 100
     .ConnectionString = "xyz"
     .Cursor Location = adUseClient
END cn

Not this format:

cn.ConnectionTimeout = 100
cn.ConnectionString = "xyz"
cn.Cursor Location = adUseClient

If you are one of those people who need to prove this claim for yourself, try running the following code:

Public Declare Function GetTickCount Lib "kernel32" () As Long
Sub T1()
Dim i As Long
Dim cn As ADODB.Connection
Dim Fast As Long
Dim Slow As Long

Set cn = New ADODB.Connection

With cn
     .ConnectionTimeout = 100
     .ConnectionString = "xyz"
     .CursorLocation = adUseClient
End With
cn.ConnectionTimeout = 100
cn.ConnectionString = "xyz"
cn.CursorLocation = adUseClient


For i = 1 To 100000
   Fast = Fast - GetTickCount
   With cn
        .ConnectionTimeout = 100
        .ConnectionString = "xyz"
        .CursorLocation = adUseClient
   End With
   Fast = Fast + GetTickCount
  
   Slow = Slow - GetTickCount
   cn.ConnectionTimeout = 100
   cn.ConnectionString = "xyz"
   cn.CursorLocation = adUseClient
   Slow = Slow + GetTickCount
Next
MsgBox "Fast=" & Fast & vbCrLf & "Slow=" & Slow
End Sub

Thanks to Gareth Edwards for the above example code.

[6.5, 7.0, 2000, 2005] Updated 12-6-2004

*****

Don't use VB objects to act as a data holder (to store data) in your SQL Server-based applications. Instead, use an array or a collection of user-defined types (UDTs). While using objects you create to store data can be convenient, it also creates a lot of unnecessary overhead. Each time you have to instantiate and then destroy an object hurts performance and scalability. How do you know if an object you have created is storing data that should be stored elsewhere? If the class has mostly properties and few if any methods, then this is a good clue. [6.5, 7.0, 2000, 2005] Updated 12-6-2004

*****

Don't instantiate, initialize, use, and then destroy an object within a loop. If the loop repeats itself much at all, you create an inordinate amount of overhead for your application. Instead, reuse the same object in the loop. One of the best ways to do this is to include a reinitialize method for the object that can be called from within the loop. [6.5, 7.0, 2000, 2005] Updated 12-6-2004

*****

For maximum performance, consolidate most, if not all, of your COM components in a single DLL. COM components instantiated from a single DLL instantiate faster than if they are called from multiple DLLs. [6.5, 7.0, 2000, 2005] Updated 12-6-2004

*****

If you decide not to use a stored procedure to access SQL Server, but instead choose to use an embedded SQL statement in your VB code, and if that embedded SQL statement will be repeated, such as in a loop, consider setting the ADO Command object's "Prepared" property to "True".

What this property does is to tell SQL Server to compile and save a copy of your SQL statement in SQL Server's cache. The first time the SQL statement is executed, the statement has to be compiled and stored in memory. But in subsequent calls, the statement is called from the cache, boosting performance because it does not have to be recompiled each time it is called.

If the SQL statement will only be executed once, then don't set this option, as it will actually decrease performance for SQL statements that are run only once. The performance boost only comes if the SQL statement is run multiple times. [7.0, 2000, 2005] Updated 12-6-2004

*****

If you use collections in your VB 6 or ASP code, consider instead using dictionaries in order to boost the performance of your application. While you are probably familiar with what and how collections are used, you may not be familiar with dictionaries. The dictionary class (the Scripting.Dictionary Object) is part of the Microsoft Scripting Runtime library, which is delivered with the VB 6 and ASP development environments.

Like collections, dictionaries can hold any type of data, and items can be retrieved by using a key, or iterated using the "For Each" syntax. But dictionaries are different in that offer properties and methods not available for collections. And the biggest difference is that they are much faster, about twice as fast. If you haven't learned about dictionaries yet, you need to take the time now to learn about their numerous advantages. [6.5, 7.0, 2000, 2005] Added 12-27-2000