Performance Tuning Tips for Creating Visual Basic Applications Using SQL Server

To speed up string manipulation in VB (never a fast performing task), consider the following suggestions that can speed string manipulation performance:

• When feasible, use fixed-length strings instead of variable-length strings.
• Try to minimize string concatenation.
• Try to minimize string comparisons.
• Try to avoid calculating the length of a string more than once. If you need this data more than once, and the string length does not change, then calculate the length once and store this value in a variable for reuse.
• When passing a string to an in-process function, try to pass it by reference instead of by value.
• Most VB string functions have two forms, one that produces a variant and one that produces a string (generally has a "$" after the function name). The version that produces the string is faster, and should generally be used.
• Consider using byte arrays instead of strings.

[6.5, 7.0, 2000, 2005] Added 12-28-2000

*****

If you still have any legacy VB applications that still use VB-SQL to access SQL Server, you may want to consider rewriting the app. VB-SQL not only provides slow access, it is no longer supported by Microsoft. [6.5] Added 1-2-2001

*****

If you are the sort of VB developer who likes to design their applications around objects, you want to keep in mind that over-encapsulating data access within objects can hurt performance. For example, from an OO design approach, you might consider encapsulating data access to each individual table in a SQL Server database, creating a separate class for each table. While this may appeal to your OO design goals, it is inefficient from a performance perspective.

Too much encapsulation can lead to situations where you don't take advantage of SQL Server's built-in optimization abilities, it causes too many round-trips to the database, and it can use more database connections than absolutely required. Instead of over-encapsulating your data access in class, a more efficient approach is to use stored procedures to encapsulate your business logic. Stored procedures eliminate these three drawbacks. [6.5, 7.0, 2000, 2005] Added 7-19-2001

*****

If you use send Transact-SQL code as part of your VB ADO code directly to SQL Server, without using a stored procedure (which we don't recommend if you want best performance, use a stored procedure instead) you want ADO to execute the Transact-SQL code using the sp_executesql system stored procedure, not for ADO to create a temporary stored procedure in one step, execute the temporary stored procedure in the second step, and then drop the temporary stored procedure in a third step. As you can imagine, this is a total of three crosses of the network, and it can greatly increase overhead and hurt performance.

How do you know if your ADO code is behaving well? Use the SQL Server Profiler to trace the activity between your VP application and SQL Server. If you see that SQL Server is creating temporary stored procedures, and not using sp_executesql, then you need to review your ADO code, looking for ways to optimize it. [7.0, 2000, 2005] Added 8-31-2001

*****

Limit the amount of rows you return from a database to populate a pick-list or drop-down box. Lots of rows not only slows down your application, it also makes it less convenient for your user to select the item or items they need. Have you ever had to select from over 100 choices? It is not easy.

If you need to give your user a lot of choices, instead of displaying them in one large pick-list or drop-down list, provide a way for the user to filter out any options that are not applicable to them. For the best performance, perform the filtering at the client, not the SQL Server.

Ideally, you should use a stored procedure to retrieve the minimum amount of rows you need, then if there are still a lot of rows to to deal with (from the user's perspective), provide a mechanism for the user to filter the list using the various ADO methods available to use for local filtering. This reduces the number of round trips from the client to SQL Server, helping to boost performance. [6.5, 7.0, 2000, 2005] Added 12-11-2001