Application Design Optimization Tips---SQL Server
ragav...@gmail.com
********************
******************************************
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.
·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.
·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.