Here are several tips for writing Transact-SQL statements, batches, stored procedures, and triggers to improve their performance:
- When connected to Microsoft® SQL Server™ version 7.0, Transact-SQL statements that use the following guidelines have a better chance that their execution plans will be reused. For more information, see Execution Plan Caching and Reuse and Building Statements at Run Time.
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3
Instead, using ODBC as an example, code the SELECT statement as:
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = ?
Use the SQLBindParameter ODBC function to bind the parameter marker (?) to a program variable and execute the statement.
In a Transact-SQL script, stored procedure, or trigger, use sp_executesql to execute the SELECT statement:
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string. */
SET @SQLString =
N'SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = @ShipID'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@ShipID int'
/* Execute the string. */
SET @IntVariable = 3
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ShipID = @IntVariable
- Use sp_executesql when you do not need the overhead of defining stored procedures. Always use sp_executesql instead of a temporary stored procedure.
- To increase the possibility that execution plans for batches will be reused on SQL Server 7.0 consider using these mechanisms:
- When multiple concurrent applications will be executing the same batch with a known set of parameters, implement the batch as a stored procedure that will be called by the applications.
- When an ADO, OLE DB, or ODBC application will be executing the same batch multiple times, consider using the PREPARE/EXECUTE model of executing the batch. Also use parameter markers bound to program variables to supply all needed input values, such as the expressions used in an UPDATE VALUES clause or in the predicates in a search condition.
- When calling a stored procedure from an ADO, OLE DB, or ODBC application, use the ODBC { CALL procedure_name } escape sequence instead of the Transact-SQL EXECUTE statement. For more information, see Calling a Stored Procedure.
- Do not keep a transaction outstanding for long periods of time. A long-standing transaction can reduce throughput by holding locks on rows for long times, preventing other connections from accessing the rows in a timely manner.
- Do not keep a result set outstanding for a long period of time. After executing a Transact-SQL batch, fully process or cancel all result sets from the batch as quickly as possible.
- Minimize the numbers of rows returned from a SELECT statement by using the WHERE and HAVING clauses to select only the rows needed by the application.
- Minimize the use of not equal operations, <> or !=. SQL Server has to scan a table or index to find all values to see if they are not equal to the value given in the expression. Try rephrasing the expression using ranges:
WHERE KeyColumn < 'TestValue' AND KeyColumn > 'TestValue'
- Reduce roundtrips between the application and the server by:
- Including multiple statements in a single batch sent from the application to the server. For more information, see Batches.
- Make intelligent use of stored procedures, especially on earlier versions of SQL Server. Putting several Transact-SQL statements in a single stored procedure reduces the amount of information that has to be sent from the application.
- Reserve the use of server cursors to when the cursor functionality is needed by the application. Instead, consider using a default result set. For more information, see Cursors. For ODBC applications, you should consider using a fast forward-only cursor with the autofetch option. For more information, see Fast Forward-Only Cursors (ODBC).
- Use advanced features available in Transact-SQL to perform work in one batch on the server instead of pulling the results to the application and then using them to send another Transact-SQL statement to SQL Server.
- Use variables and control-of-flow statements to build logic into batches, stored procedures, and triggers instead of pulling large result sets to the client and performing the logic there. For more information, see Using Multiple Statements.
- Use constructs such as CASE to include logic in individual Transact-SQL statements. For more information, see Using CASE.
- Use the UPDATE statement with the FROM clause to update values in one table using values from other tables in one operation instead of selecting the source result set to the client and then updating the target table one row at a time.
- Take full advantage of the join capabilities of SQL Server. For more information, see Join Fundamentals.
- Within a batch, keep all data definition language (DDL) statements for a temporary table together, for example:
/* Example 1. */
CREATE TABLE #temp1 (ColA INT NOT NULL)
CREATE UNIQUE INDEX MyIndex ON #temp1(ColA)
INSERT INTO #temp1 SELECT IntCol FROM SomeTable
SELECT * FROM #temp1
GO
This is another example of a good batch:
/* Example 2. */
CREATE TABLE #temp1 (ColA INT UNIQUE NOT NULL)
INSERT INTO #temp1 SELECT IntCol FROM SomeTable
SELECT * FROM #temp1
GO
Do not code:
/* Example 3. */
CREATE TABLE #temp1 (ColA INT NOT NULL)
INSERT INTO #temp1 SELECT IntCol FROM SomeTable
CREATE UNIQUE INDEX MyIndex ON #temp1(ColA)
SELECT * FROM #temp1
GO
Each time a DDL operation is performed on a temporary table, all batches that refer to it must be recompiled. The query optimizer ensures that the CREATE statements in examples 1 and 2 are done in one operation and the batches are only recompiled once. In example 3, the INSERT statement between the two CREATE statements forces a separate recompile for each CREATE statement.
- Minimize the use of temporary tables as places to store intermediate results in a series of Transact-SQL statements. Some logic is too complex to perform in a single Transact-SQL statement. In these cases, you must code multiple Transact-SQL statements and use temporary tables to pass the results of one statement to the next. Creating and maintaining the temporary tables requires overhead; if possible, consider coding the operation as a single, more complex Transact-SQL statement.
- In SQL Server 7.0, use of temporary tables in stored procedures and triggers may cause the stored procedure or trigger to be recompiled every time it is used. To avoid such recompilation in version 7.0, stored procedures or triggers that use temporary tables must meet the following requirements:
- In the stored procedure or trigger, all statements that contain the name of a temporary table must refer to a temporary table created in the same stored procedure. The temporary table cannot have been created in a calling or called stored procedure, or in a string executed using EXECUTE or sp_executesql.
- All statements that contain the name of a temporary table must appear syntactically after its creation in the stored procedure or trigger.
- The stored procedure or trigger cannot contain any DECLARE CURSOR statement whose SELECT statement references a temporary table.
- All statements that contain the name of any temporary table must precede any DROP TABLE statement that references a temporary table. DROP TABLE statements are not needed for temporary tables created in a stored procedure; the tables are automatically dropped when the procedure terminates.
- No statements creating a temporary table (such as CREATE TABLE or SELECT INTO) may appear in a control-of-flow statement such as IF...ELSE or WHILE.
- To prevent issues with the interpretation of centuries in dates, do not specify years using two digits, for example:
/* Do this. */
SELECT *
FROM Northwind.dbo.Orders
WHERE OrderDate > '12/31/1997'
/* Do not do this. */
SELECT *
FROM Northwind.dbo.Orders
WHERE OrderDate > '12/31/97'