HomeMySQL Page 3 - SQL Performance and Tuning Considerations, concluded
Microsoft SQL Server Considerations - MySQL
This article, the second of two parts, will show you how to make the SQL statements you write run faster and more efficiently. It is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).
This section covers some tuning considerations that are speciﬁc to Microsoft SQL Server databases.
SQL Server Query Performance Considerations
Here are some considerations for running queries in Microsoft SQL Server databases:
Access via stored procedures. Placing frequently used SQL queries in stored procedures can yield signiﬁcant performance gains. In fact, in older versions of Microsoft SQL Server, it was essential to do so in order to achieve reasonable performance because there was no SQL cache to hold recently used SQL statements. In SQL Server 2005, the SQL cache allows the DBMS to recognize a statement similar to one that has already been run and to bypass statement preparation and optimization steps when the statement is reused from the cache. Nonetheless, any statement that is accessed through a stored procedure is precompiled and optimized, so it will always perform well (even if it is not found in the cache at the time it is run). This is one reason why seasoned developers strive to do most or all of their SQL Server database access using stored procedures.
Avoid nulls in unique indexes. Unlike most other SQL implementations, unique indexes in SQL Server do not handle null column values well. You can deﬁne a unique index that includes a column that is allowed to be null, but the second index entry that contains a null is considered a duplicate of the ﬁrst, and therefore results in a duplicate key error. Most other SQL implementations avoid this issue by not indexing null values.
Consider clustered indexes. A clustered index causes the table rows to be physically ordered by the indexed column(s). This can help considerably in search and join operations, but itís at the expense of table maintenance, particularly when new rows force existing rows to be moved to maintain their sequence.