Application Design Optimization Tips

Here are fifteen tips that you can use to ensure your Application Designs are performing in the most efficient manner possible.

  1. Use stored procedures instead of passing ANSI-compliant SQL
    to the database.

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

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

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

  4. 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.

  5. Consider designing the application to take advantage of the n-tier
    application model.

    By using the n-tier application model, you can increase the application’s
    performance and scalability.

  6. Try to restrict the result sets by using the WHERE clause in your
    SELECT statements.

    This can result in good performance benefits because SQL Server will
    return to the client only particular rows, not all rows from the table(s).
    This can reduce network traffic and boost the overall performance of
    the query.

  7. Try to restrict the result sets by returning only the particular
    columns from the table, not all of the table’s columns.

    This can result in good performance benefits because SQL Server will
    return to the client only particular columns, rahter than all of the table’s columns. This
    can reduce network traffic and boost the overall performance of the query.

  8. Try to restrict the result sets by using the select statements
    with the TOP keyword.

    This can improve the performance of your application because a smaller
    result set will be returned. This can also reduce the traffic between
    the server and the client.

  9. Use SQL Server cursors to allow your application to fetch a small
    subset of rows instead of fetching all of the table’s rows.

    SQL Server cursors allow the 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 a
    smaller result set will be returned.

  10. 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 using OLE DB for developing tools, utilities,
    or low-level components that need high performance and using ADO
    for general-purpose access programs in business applications
    (Accounting, Human Resources, and Customer Management).

  11. When you connect to SQL Server, use ‘Microsoft OLE DB Provider
    for SQL Server’ instead of ‘Microsoft ODBC Driver for SQL Server’.

    Because the native OLE DB provider is faster than the ODBC provider, you
    should use the OLE DB provider whenever possible.

  12. 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 is exceeded, the blocked statement
    is 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 the error
    message 1222.

  13. 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.

  14. Try to avoid using the Refresh method when you call stored procedures
    from the ADO Command object.

    This can improve performance of your application because using the 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.

  15. 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.

»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles