SQL Server SQL statement Tuning tips

With examples and resolution plans, this article shows some tips for improving query efficiency on Microsoft SQL Server. There are many small tips and tricks in programming. Knowing these techniques can extend your ability to optimize your performance.

All of our examples in this section choose to use the Microsoft Showplan_all output because it is more compact and exhibits typical information. (Sybase's query plan is basically the same, and may contain some other information) Most of the examples are either based on the pubs database or on a standard system table. We've expanded the tables we used in the pubs database, adding tens of thousands of lines to many tables.

Sub query optimization

A good and commendable rule is to use joins instead of all subqueries. The optimizer can sometimes "flatten" subqueries automatically and replace them with regular or outer joins. But that doesn't always work. A clear connection gives you more options for choosing the order of the tables and finding the most likely plan. When you optimize a particular query, it can make a big difference whether you get rid of the query or not.

Example

The following query selects the names of all tables in the pubs database, and the clustered index (if any) for each table. If you do not have a clustered index, the table name still appears in the list and is displayed as a dashed line in the clustered index column. Two queries return the same result set, but the first one uses a subquery and the second uses an outer join. Compare query plans generated by Microsoft SQL Server:

Without further exploration, we can see that the connection is faster in terms of CPU and total elapsed time, requiring only half of the subquery logic to read. In addition, the two cases are accompanied by the same result set, although the order of the sorts is different, because the connection query (because of its GROUP BY clause) has an implicitly ordered by: