Basic Tenets of Designing for Performance

Designing for performance requires making tradeoffs. To get the best write performance out of your database, you must sacrifice read performance. Before tackling database design issues for your application, it is critical to understand your goals. Do you want faster read performance? Write performance? A more understandable design?

Following are some basic truths about physical database design for SQL Server 2000 and their performance implications:

• Keep table row sizes as small as possible. This is not about saving disk space; smaller rows mean more rows will fit on a single 8KB page, which means less physical disk reads are required to read a given quantity of rows.

• Use indexes to speed up read access, but

• The more indexes that a table has, the longer it takes to insert, update, and delete rows from that table.

• Using triggers to perform any kind of work during an insert, update, or delete will exact a performance toll, and decrease concurrency by lengthening transaction duration.

• Implementing declarative referential integrity (primary and foreign keys) helps maintain data integrity, but enforcing foreign key constraints requires extra lookups on the primary key table to ensure existence.

• Using ON DELETE CASCADE referential integrity constraints likewise helps maintain data integrity, but requires extra work on the server's part.

Keeping tables as narrow as possible ”that is, ensuring that the row size is as small as possible ”is one of the most important things you can do to ensure your database performs well. To keep your tables narrow, choose column data types with size in mind. Don't use an int datatype if a tinyint will do. If you have zero-to-one relationships in your tables, consider vertically partitioning your table. (See "Vertical Data Partitioning" under the "Denormalizing the Database" section later in this chapter for details on this scenario.)

Cascading deletes (and updates) causes extra lookups to be done whenever a delete runs against the parent table. In many cases, the optimizer will use worktables to resolve delete and update queries. Enforcing these constraints manually, from within stored procedures, for example, can give better performance. This is not a wholehearted endorsement against referential integrity constraints. In most cases, the extra performance hit is worth the saved aggravation of coding everything by hand. However, you should be aware of the cost of this convenience.