Optimizer Hints - Microsoft SQL Server Black Book

You have the ability to tell Microsoft SQL Server that you wish a certain action to be performed without the optimizer using its normal methods. This is good in some cases and a disaster in others. Use optimizer hints with great caution. Many years of programming have gone into the design and optimization of your queries in the built-in optimizer.

Although no tool is perfect, the Query Optimizer checks many things on the fly each time a query is run. In most situations the Query Optimizer will choose the correct plan based on the existing state of your tables and indexes.

I use optimizer hints in two situations. The first is when I am reading data that is highly fluid. I use the (NOLOCK) option in my SELECT statements to be sure that I am not locking pages in my queries when someone else is trying to do something. See Listing 5.12 for an example of a query with the NOLOCK optimizer hint.

Listing 5.12 Reading data without locking pages.

/* Select ID and Name without locking */ SELECT 'Author ID' = au_id,

'Name' = UPPER(SUBSTRING(authors.au_fname,1,1)) + '. ' + SUBSTRING(authors.au_lname,1, 15) FROM authors (NOLOCK)

ORDER BY au_lname

The results are the same as they would have been without the hint, but we have avoided the risk of creating a deadlock or wait state on the server. At least every other day I find a message among Internet newsgroups about locking, hanging, or slow queries. In some of those situations, this hint would solve the problem.

http://www.itknowledge.com/reference/standard/1576101495/ch05/163-165.html (1 of 3) [1/27/2000 6:16:18 PM] Go!

Keyword

---

Highly active data benefits from the NOLOCK hint because the query will not attempt to place any shared locks on the pages SQL Server must access for the query. If a client does not read all the records it has requested from the server, Microsoft SQL Server will hold those pages with a shared lock until the last record has been returned to the client. Shared locks can cause problems with very active data.

The second situation in which I use optimizer hints is when I want to lock data sets or a table. I will specify a table-level lock to reduce overhead and ensure I have the whole table when I need it. See Table 5.4 for a list of optimizer hints and their uses in your queries.

Table 5.4 Optimizer hints and their uses.

Hint Intended Use

NOLOCK Does not lock any data pages for a given query on the specified table. No shared locks will be issued to Microsoft SQL Server and exclusive locks will be ignored. This statement basically means to perform a “dirty read” of exactly what is in the data pages at the time of query execution.

INDEX = Specifies which index to use for this query. Forcing the index may not improve performance of your queries. You can also specify a table scan with this hint.

HOLDLOCK Holds the lock on data pages until the transaction is completed. You cannot use HOLDLOCK in SELECT statements that include FOR BROWSE.

UPDLOCK Issues update locks instead of shared locks when reading data. Holds locks until the end of transaction or statement execution. PAGLOCK Forces single shared page locks when a table lock may have been

issued.

TABLOCK Places a single shared lock on the entire table until the transaction is completed.

TABLOCKX Requests an exclusive lock of the table. This lock will be held until the transaction is completed.

FASTFIRSTROW The optimizer will use a nonclustered index to honor the ORDER BY statement, if one exists, to return the first row of a query as fast as possible. This method is not always faster overall for larger result sets. Look up FASTFIRSTROW in Microsoft SQL Server Books Online before issuing this optimizer hint. I do not usually use this hint on production systems.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book (Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

Subqueries

To improve the intelligence of your code, you can nest a query inside your queries to return information to the outer query. Any place an expression is valid in a query, you can place another query to evaluate and return a value for use in your SQL code. INSERT, UPDATE, DELETE, and SELECT statements support expressions in many places. In many situations, a subquery can be rewritten as a join condition to return the same results. In some situations, a join condition can actually perform your query faster than a subquery. Triggers can be made more intelligent through subqueries as well.

I write a lot of table-driven stored procedures that will run with only a few parameters but may query many user or system tables to actually perform the tasks internally. By reducing the amount of data required to perform a given task, this can substantially simplify the client code. There are two types of subqueries: noncorrelated and correlated. An example of a noncorrelated subquery is one that will run before the outer query to return its results to the outer query for execution. This nesting is very similar to the execution of a function call in your code. A

correlated subquery references the outer query internally and will be executed one time for each of the matching rows in the WHERE clause of the outer query. This might sound confusing at first, but it makes sense if you think about what actually happens.

When the outer query executes, it might only have five rows of a thousand-row table returned. If the inner query must be executed one time for each of the thousand rows checked, the process will be slow. By referencing the outer query’s WHERE clause in the inner query, you are telling the optimizer to first filter out the thousand rows and perform the subquery five times instead of a thousand.

A subquery uses the same syntax as a regular query. You must, however, adhere to a few restrictions when using subqueries. A subquery is always enclosed in parentheses. It can only specify one column name of any data type except text and image. Any DISTINCT option will be maintained inside the subquery only. If you are testing for equality, you can only return a single

http://www.itknowledge.com/reference/standard/1576101495/ch05/165-167.html (1 of 3) [1/27/2000 6:16:19 PM]

Go!

Keyword

---

item. If you are using a subquery in a WHERE clause and use the IN option for a list, the subquery can return more than one row. See Listing 5.13 for an example of a subquery and its syntax.

Listing 5.13 Subquery example.

/* Percentage of total sales by quantity */ SELECT 'Title ID' = title_id,

'Sales' = qty,

'% of Total' = CONVERT(NUMERIC(5,2),

(CONVERT(NUMERIC,qty) /

(SELECT SUM(qty) FROM sales)) * 100) FROM sales

ORDER BY qty DESC Output

Title ID Sales % of Total --- --- --- PS2091 75 15.21 PC8888 50 10.14 TC3218 40 8.11 BU2075 35 7.10 PC1035 30 6.09 MC3021 25 5.07 PS2106 25 5.07 PS7777 25 5.07 BU1111 25 5.07 TC4203 20 4.06 TC7777 20 4.06 PS2091 20 4.06 PS1372 20 4.06 PS3333 15 3.04 BU7832 15 3.04 MC3021 15 3.04 PS2091 10 2.03 MC2222 10 2.03 BU1032 10 2.03 BU1032 5 1.01 PS2091 3 0.61 (21 row(s) affected)

Union

You can use the union operator to combine two or more result sets into a single result set. This operation is used in many systems to provide for the combination of current and historical data. If your system archives data into a history table, to keep the current working set of data smaller and more manageable, the union operator could be used to combine your current data and historical data when performing statistical analysis or archive look-up queries.

By default the union operator will remove any duplicate rows, which can be very useful when combining data from more than one source. An important issue with the union operator is that

the SELECT lists for both queries must match exactly. Number of columns, data type, and order are very important in performing a union between two queries. If your query contains an

ORDER BY statement, the entire final result set will be sorted based on your ORDER BY statement.

The output result set will pull the column headings from the first query. If you wish to replace the default column headers with legible English, you must modify the SELECT list of the first query. The WHERE clauses do not have to match in either query. You can also use placeholders for missing columns in a union to fill in any missing information in one table or another in your queries. For more on the use of unions in your SQL code, see Microsoft SQL Server Books Online.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book

(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

In document Microsoft SQL Server Black Book (Page 152-158)