and Its Disadvantages

…and Its Disadvantages

The following are the standard problems with a "simple" solution based on a single query:

• The query joins many tables Specifying a number of tables that is acceptable is difficult, but 15 or 20, and sometimes even 10, is too many. SQL Server has to do a considerable amount of work to join them all. A normalized set of tables is optimal for modifying data, but for querying, designers should explore denormalization of the model.

• (B) locking If users are accessing the same set of tables for both updating and querying, they will block each other. SQL Server puts a shared lock on records that qualify as a result of a query while the query is in progress. Other queries are not blocked by it and can be executed at the same time. However, those locks will prevent modifications of the records until the query is done. On the other hand, when a transaction is modifying a record, users will have to wait for the transaction to be completed to have the results to their queries returned.

• The complete result set is sent to the client Too often, users specify criteria that are not selective enough and may return hundreds (or thousands) of records. Such a recordset is seldom required and users will not browse through it. Typically, they will modify the original criteria and make the query more selective to get a subset of the original recordset.

• Sorting Users expect the result set to be sorted and they also expect to be able to change the sort order on-the-fly. These actions require processing power.

• Table scans Some queries may contain criteria that are not optimizable (SARG-able). A common example occurs when a user requests all records for which the Name field contains a specified string. The query is implemented using the Like operator with wild cards (%) at the beginning and the end of the string, such as

        Where name Like '%str%' 

  SQL Server will not be able to use the index on the name field to process such a query.

• Ad hoc queries Since the user has the freedom to specify an unknown number and combination of criteria, queries are typically assembled dynamically. The disadvantage to this flexibility is that you do not have control over these queries, which opens up code management, optimization, and security issues. On the other hand, a dynamically created query allows an experienced user to restrict the query to only those elements that are really needed. You can drop unnecessary tables and segments of criteria.

• Improper indexes You should review indexes that are created on tables. Keep in mind that indexes are optimal for querying but not optimal for modifying data. They become overhead on transactions.