Performance Tuning T-SQL

I often receive e-mail from readers of my Web site column in which those readers talk about their Dilbert scenarios. One that amused me the most was from a DBA who was frustrated with his company direction constantly changing, due to bad database performance:

I read your article about performance tuning and thought I'd send you a quick note to tell you about my company's scenario. Through various corporate directives over the past year, we have changed our primary database five times. From SQL Server to DB2 to Oracle to MySQL, then back to SQL Server. All of this because management wants to receive better performance out of their applications and is able to place more clients on one server. Well, my head is now bruised from banging it against the wall. We have triggers with elaborate cursors, and triggers handle all of our referential integrity. No wonder we have performance problems. Not to mention the application's queries! When will they learn that a poorly written application is going to run poorly on any operating system?

This last statement is so true it must be said twice. If you have poorly designed queries, they'll run poorly on Oracle or SQL Server. In this section, I hope to outline some of the gotchas that occur when programming queries.

Performance Tips for Queries

Here are a few performance tips that can resolve common problems:

  • Carefully monitor the indexes on the table in your queries to make sure your queries are using them.

  • Try not to use WHERE clauses that don't use SARG logic. For example, OR, <>, !=, !<, !>, IS NULL, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE clauses cannot use the SARG logic, which slows down the Query Optimizer drastically. You may find that some of your queries that use these clauses are not using indexes.

  • Avoid using cursors whenever possible. Before moving forward with the cursor, see if you can do the same operation with a normal query. Consider using a temp table instead of the cursor, since there is less overhead involved.

  • Avoid using UNION statements, unless you're removing duplicate rows. Instead, use a UNION ALL statement, which is much faster and doesn't look for duplicate rows.

  • Always list your column names when performing a SELECT statement. If a column is added in the schema, it could harm your application if not properly handled, since you're pulling down a larger resultset than the application is expecting. Your network time could be much reduced if you only return data that you need.

  • Consider breaking large tables into smaller views. A good view could take a subset of the records based on a date or location.

  • If you don't have a requirement to remove duplicates or order the data, avoid using an ORDER BY or DISTINCT statement. If there is no clustered index on the column to satisfy the query, a temporary workspace must be created to fulfill the query, which can take quite a long time for large tables.

  • Use the EXISTS clause rather than the IN clause. The EXISTS clause is slightly faster.

  • If you're accessing remote data through a linked server, use the openquery() function instead of the four-part qualifier. (I'll cover this in much more detail in Chapter 9.)

  • Use the TOP statement if you need a limited amount of records.

Non-logged SQL Statements

We can't complete a T-SQL performance tuning section without adding information about other SQL statements. The following tips could help speed up delete and insert processes in your applications:

  • The TRUNCATE TABLE <table name> command is much faster than the DELETE command, since the former command is non-logged. Non-logged statements only write minimal data into the transaction log. Because the command is non-logged, there is an element of danger since it is not as easily recovered if you make a mistake. This command can only be run on tables that don't have foreign keys. The TRUNCATE TABLE command does not support any type of WHERE clause. You must be a dbo or table owner to issue a TRUNCATE TABLE statement.

  • Use commands that minimize logging, such as BULK INSERT, TRUNCATE TABLE, and SELECT…INTO. For text, ntext, and image fields, use WRITETEXT and UPDATETEXT commands, which lower the amount of logging.

  • Add table locks on inserts where you can. For example, if you're performing a data load at 2:00 A.M. and you're not worried about anyone reading from the table, you can easily use a table lock. You can do this with the TABLOCK hint when you issue a BULK INSERT command.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net