Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 510

Table 14-5: Tradeoff Matrix for Relational and Non-Relational Structures

Flat Relational Comparison

Y N Single SQL Statement for read, updates, and deletes

N Y JOIN Required

N Y Physical model looks like logical model

N Y Page scan requires minimal I/O

N Y Only applicable columns are stored

Y N Optimized for reads

N Y Optimized for writes

If you looked at your performance requirements and you have heavy emphasis on report roles and little
emphasis on transactional roles, the flat implementation would be the better choice. However, if you
have heavy transactional requirements, opt for the relational solution. Remember that you can still get
to this type of structure by processing the data in an offline period to build cubes as long as your user
response time requirements can still be met. It is generally easier to go from data that are more granular
and work back to summarized data than to work the other way around.

ID-BasedPartitioning
If during your analysis of user performance requirements you discover that you have quite a bit of user
segmentation, make sure that you design the capabilities into the tables to enable separation directly to
these user segments. User segmentation is indicative of product-based software development. If your
application is provided as a service, each user segment probably uses the software in a separate environ-
ment. User segments limit data to specific customer-based information, not as representative of security
levels, but more on a customer level. One easy way to segment an application is to create a copy of the
database and create a new environment, whether it is a website or a file share that the program can use
to connect to the database. Another way to segment can be as simple as adding an identifier for the
user segment into each table in the database. For performance, don't just think that you can provide this
separation via a relationship. It is not good enough to have the segment information through a relation-
ship with the user login; you need the attribute of segmentation directly on the transactional entities
themselves.

Denormalization for Performance
As you develop in both OLTP and OLAP database environments, you really start to get the fact that
OLTP is all about maintaining the integrity and speed of the write operation. Therefore, as you model
out the physical structure of the database, the write operation becomes the main concern from a perfor-
mance perspective. The problem is that everything can be done at a cost. The offsetting cost of the write
optimization is the creation of performance problems for the read operations. It is difficult to keep a bal-
ance when you have heavy reporting requirements from your OLTP database. One of the compromises