SQL Tips - Application Design Optimization Tips

*****

Use stored procedures instead of passing ANSI-compliant SQL to the database.
This can reduce network traffic because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

*****

Design the application to run queries asynchronously.
This can improve performance of your application because one query will not wait for the next before it can run.

*****

Consider using Microsoft Transaction Server (MTS) for object pooling.
This can improve performance of your application because MTS allows COM objects to be pooled.

*****

If most of the users have modern power computers ('fat' clients), consider design application to make client data caching.
By doing so, you can reduce the load of your SQL Server because when users will need to access the data they will use local desktop resources, not SQL Server resources.

*****

Consider designing the application to take advantage of the n-tier application model.
By using the n-tier application model, you can increase application's performance and scalability.

*****

Try to restrict the result sets by using the WHERE clause in your SELECT statements.
This can results in good performance benefits because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

*****

Try to restrict the result sets by returning only the particular columns from the table, not all table's columns.
This can results in good performance benefits because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

*****

Try to restrict the result sets by using the select statements with the TOP keyword.
This can improve performance of your application because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

*****

Use SQL Server cursors to allow your application to fetch a small subset of rows instead of fetching all table's rows.
SQL Server cursors allow application to fetch any block of rows from the result set, including the next n rows, the previous n rows, or n rows starting at a certain row number in the result set. Using SQL Server cursors can reduce network traffic because the smaller result set will be returned.

*****

Use ADO or OLE DB for accessing data from the applications that need high performance.
This can improve performance of your application in comparison with using DAO or ODBC. OLE DB is a low-level COM API for accessing data and ADO is an application-level interface that uses OLE DB. Microsoft recommends to use OLE DB for developing tools, utilities, or low-level components that need high performance and use ADO for general-purpose access programs in business applications (Accounting, Human Resources, and Customer Management).

*****

When you connect to SQL Server, use 'Microsoft OLE DB Provider for SQL Server' instead of 'Microsoft ODBC Driver for SQL Server'.
Because native OLE DB provider is faster than ODBC provider, you should use OLE DB provider whenever possible.

*****

Set a lock time-out so that queries used in your application will not run indefinitely.
You can use the SET LOCK_TIMEOUT command to allow an application to set a maximum time that a statement waits on a blocked resource. When the LOCK_TIMEOUT setting will be exceed, the blocked statement will be canceled automatically, and error message 1222 "Lock request time-out period exceeded" will be returned to the application. Your application should have an error handler that can trap error message 1222.

*****

Avoid using both OLTP and OLAP transactions within the same database.
Because OLTP transactions are optimal for managing changing data and OLAP transactions are optimal for data queries that do not change data try to relegate OLTP and OLAP transactions to their own databases.

*****

Try to avoid using Refresh method when you call stored procedures from the ADO Command object.
This can improve performance of your application because using Refresh method produces extra network traffic. You should explicitly create the stored procedure parameters using ADO code, instead of using the Refresh method to identify the parameters of a stored procedure.

*****

Avoid creating transactions using ADO's methods.
Try to create transactions inside a stored procedure on the SQL Server. By doing so, you can reduce network traffic and boost overall application performance.