Tip 9:
Don't Create Too Many Indexes
Some database administrators try to anticipate every possible sort and
search combination by creating indexes on practically every column in every
table. Having too many indexes impedes your system in many ways. Every time you
perform an insert or delete, you have to modify the indexes and the data. When
you update an indexed column, the SQL Server engine updates all affected
indexes, an action that can have the added undesirable effect of causing the
engine to restructure the index trees. This update operation can impede
performance for all applications accessing the table and can even briefly
degrade response across the entire system. You have no way of knowing whether
the engine has restructured the index trees. Extra indexes also consume more
disk space. Finally, when confronted with too many indexes, the optimizer may
not choose the best-qualified index. Your database operation may run more slowly
than if you had fewer indexes.
The best way to know whether you have too many indexes is to test your
database operations with SHOWPLAN. Simulate a typical work day, remove the
SHOWPLAN command from any procedures or code that you modified, and then review
the output. You can quickly determine which index SQL Server is using and then
remove any indexes that the engine doesn't reference often.
Sometimes you need additional indexes to handle specific, easily
identifiable tasks, such as an end-of-month processing suite. In these cases,
create the indexes immediately before you need them and drop them as soon as you
finish. At other times, you need to run large batch update operations, which can
be time-consuming if you have too many indexes to update. You can benefit from
creating a stored procedure to drop some indexes, perform the operation, and
then re-create the indexes. The overall time to do this can be less than if you
let the batch update operation alter the extra indexes.
Tip 10:
Use the Multiple Table DELETE Option
Traditional SQL limits your delete operations to one table at a time.
Transact-SQL has a multi-table delete capability that reduces the number of
individual engine calls. For example, to delete rows in two tables, resources
and parts, you can issue two SQL statements:
delete from resources where resource_cost > 5000
delete from parts where part_cost > 5000
Or you can use Transact-SQL's multiple table DELETE extension:
delete from resources
from parts
where resources.resource_cost = parts.part_cost
and resources.resource_cost > 5000
This approach is not portable, so you can't run your application against
different databases. But if you work only with SQL Server, multi-table is a
convenient shortcut. You can also use the UPDATE statement to alter several
tables at one time.
A Last Word
As you experiment with these tips, change only one parameter at a time so
you know which change produces which effect. And be sure you back up your
database before and after each change.
For more information about these tips and other suggestions for enhancing
SQL Server's performance, read my book, Microsoft SQL Server: Planning and
Building a High-Performance Database.
Microsoft SQL Server : Planning
and Building a High-Performance Database
Author: Robert D. Schneider
Publisher: Prentice Hall PTR
Upper Saddle River, N.J., 1997
Register
Tip 10 is about a delete statement that uses multiple tables. A delete statement can affect only one table at a time but can use the contents of other tables to decide which rows to delete. The article implies that one SQL statement can delete rows from multiple tables at the same time. This capability has never been possible with any major RDBMS, and in fact, is against the ANSI SQL standard.
Try the following simple code, which is the SQL presented in the article with some statements added to create the tables and populate them. Review the contents after the delete statement has completed.<br><br>
create table resources
(resource_cost money)<br>
create table parts (part_cost money)<br>
insert resources values ($1000)<br>
insert resources values ($6000)<br>
insert parts values ($1000)<br>
insert parts values ($6000)<br>
delete from resources from parts<br>
where resources.resource_cost = parts.part_cost<br>
and resources.resource_cost> $5000<br><br>
select * from resources<br>
select * from parts<br><br>
The only rows the delete statement affects are the rows in the resources table.
Schneider complicates the problem by including a join clause in the delete statement. Unless you have a true one-to-one relationship between the resources and parts table, you will delete rows only where you have a match on the cost columns. This is not the equivalent of deleting all rows from each individual table where the cost is greater than 5000.<br>
--Lawrence Rogers
Lawrence Rogers August 12, 1999