Optimizer Hints - Microsoft SQL Server Black Book pdf

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

---Go!

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

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

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

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

---Go!

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,

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

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

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

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

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

INSERT Statements

Up to this point, I have focused on the retrieval of data. In a client-server environment, you have two real processes for which you must program: the presentation of data to the user and the modification or creation of data on the server. Presenting data to the client applications does not typically modify data or create transaction log entries. Modification of data will place entries into the Syslogs table and generate transactions on your server.

I have saved discussion of these statements until now because, as shown in Listing 5.14, much of the syntax is identical to that of the SELECT statement. You must specify the table into which you wish to insert data and the columns for which you are passing data. You are not required to pass data for all columns. Any column for which you have a default value defined or that allows a NULL value can be omitted from the column list.

Notice that you can use a SELECT statement to query for the values you wish to insert into a table. In the Practical Guide section of this chapter, I will walk you through using an INSERT/SELECT statement to populate a table with data from another source.

Listing 5.14 INSERT statement syntax.

/* Insert Statement */

INSERT [INTO]{table_name | view_name}

[ ( column_list ) ]

{DEFAULT VALUES | values_list | select_statement}

INSERT statements typically involve a single row (unless an INSERT/SELECT is used) and will place the data you specify into the table and update any indexes with the appropriate values. This is important. The more indexes you have on the table, the longer an insert will take. If you are performing a nightly insert process of many rows with little activity on the table otherwise, you might consider dropping the indexes prior to the insert operation and rebuilding them when you are finished. This method would result in better performance and would increase the integrity of your indexes.

NOTE: On occasion, Microsoft SQL Server generates some index/insert-related error messages. By using INSERT/SELECT statements when inserting many records after dropping any existing indexes, you can reduce the possibility of this type of error. If you have not applied Service Pack 2 for Microsoft SQL Server 6.5, you should do so as soon as possible, because it contains some fixes that are index- and insert-related.

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

Go!

Keyword

---Go!

Listing 5.15 shows how to insert records into a table. I have created a record in the Authors table with my information as an example. This information is provided as an example only—it will not exist in your table unless you run this query on your table.

Listing 5.15 Sample insert into the Authors table.

/* Sample insert into Authors table (not formatted)*/

INSERT INTO authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES ('123-45-6789', 'Dalton', 'Patrick', '502 555-1234', '123 Main Street', 'Shepherdsville', 'KY', '40165', 1)

/* Same Query formatted in line */

INSERT INTO authors

(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES

('123-45-6788', 'Dalton', 'Patrick', '502 555-1235', '456 Main Street', 'Shepherdsville', 'KY', '40165', 1)

/* Formatted in column form */

INSERT INTO authors

/* If you execute all 3 queries, 3 records will be inserted into the Authors table. */

Notice that I omitted the column list from the third query. I can do this because I am supplying all the values in the exact order they are specified in the table schematic. I could have reordered the columns any way I wanted, as long as the column list and the values list are the same. You can pass string literals, variables, or explicit NULL values in an INSERT statement. INSERT statements return the number of rows affected to the client and to the global variable @@rowcount. You must select the row again to view the data after it has been inserted.

INSERT statements affect one table at a time and cannot be combined to add rows to multiple tables at once.

You must issue a separate INSERT statement for each table you wish to modify.

Identity Columns

In Microsoft SQL Server the identity column generates a unique number for a record when that record is inserted into the table. This identity can be incremented or decremented by 1 or any other number and maintained automatically by Microsoft SQL Server. You do not supply a value for this column when you insert records. Should you wish to override the automatic generation of the next number for an identity column, you can set a property for the table called “identity insert.” See Microsoft SQL Server Books Online for more information on the identity column and its uses.

Stored Procedures

Stored procedures can be used to optimize SQL code execution and the methods of communication between client applications and Microsoft SQL Server. Stored procedures can have parameters with specific data types passed to them for execution of SELECT, INSERT, UPDATE, and DELETE statements. Return codes

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

and output param eters are also useful in developing client interfaces to Microsoft SQL Server. To use a stored procedure in your scripts, you would use the EXECUTE statement preceding the stored procedure and parameter list. See Chapter 6 for more information on the use and syntax of stored procedures.

Triggers

INSERT statements cause any defined insert trigger to be fired each time the INSERT statement is issued. If you are using INSERT/SELECT, you will place multiple rows into the virtual table that is “inserted.” The inserted table is used for and by the triggers that are placed on a table. The schema of the inserted table matches the structure of the target table exactly and will contain any records that are to be inserted into the target table containing a trigger. A trigger fires one time for each insert, not one time for each row.

Previous Table of Contents Next

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

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 pdf (Page 152-161)