Database Reference
In-Depth Information
Figure 10-4: Adding a Customer Order Item table allows for the addition of granularity to the data.
Properly index your tables: A key consideration in analytics is query performance. You may
have reports that are hitting your system live. You do not want those reports to perform
poorly, because that would lead to a bad user experience. Indexing is the most effective tool
you have in SQL for speeding up queries running against large tables. We review indexing
concepts in a later section of this chapter.
Keep your tables narrow: SQL Server (and relational databases in general) store each row in
your table in one location on disk. Therefore, the larger the row, the longer it takes for SQL
Server to read or update it. For this reason, keep the tables that have many rows in them
narrow. This enables SQL Server to read and write data from these tables much faster and
leads to better system performance.
Consider full data reload requirements
Another important factor to consider in your design is the frequency and need for full reloads. Full
reloads are not common, but they do occur. Your objective is to minimize them, but if you have a
business or technical requirement to support a full reload, your design needs to able to handle such a
request. Two primary concerns are related to this:
Full reload performance: You can design a system that performs relatively quickly when
you're doing incremental updates, but you may find that it's slow when it comes to full
reloads. This may be an issue if you have a limited window to perform full reloads. There are
several factors to consider when performing a full reload, but the primary one is indexing.
Loading a table with multiple indexes is slow in a relational database. The most optimal
approach is to drop and re-create your indexes before and after a full reload.
 
Search WWH ::




Custom Search