Microsoft Office Tutorials and References
In Depth Information
On the other hand, it would be very easy to query the table if it were a little bit denormalized and had some data
pre-populated, as shown in Figure 1-4 . In this case, the number of joins is reduced, thereby shortening the T-SQL
query. This simplifies the query and improves the performance. However, the performance depends on the efficiency
of indexing. Further, denormalizing the tables causes excessive I/O.
Figure 1-4. The denormalized table
■ as you can see in Figure 1-4 , the t-sQl query would be simplified, but denormalized tables can cause
excessive i/O because they contain fewer records on a page. it depends on the efficiency of the indexing. the data and
indexes also consume more disk space than normalized data.
You might wonder why you can’t simply run these queries on your OLTP database without worries about
performance. Or create views. Simply put, OLTP databases are meant for regular transactions that happen every day
in your organization. These are real-time and current at any point of time, which makes OLTP a desirable model.
However, this model is not designed to run powerful analyses on these databases. It’s not that you can’t run formulas
or aggregates, it’s that the database might have been built to support most of the applications running in your
organization and when you try to do the analysis, these applications take longer to run. You don’t want your queries to
interfere with or block the daily operations of your system.
■ to scale operations, some organizations split an Oltp database into two separate databases (that is, they
replicate the database). One database handles only write operations, while the other is used for read operations on the
tables (after the transactions take place). through code, applications manage the data so that it is written to one database
and read for presentation from another. this way, transactions take place on one database and analysis can happen on
the second. this might not be suitable for every organization.
So what can you do? Archive the database! One way that many organizations are able to run their analyses on
OLTP databases is to simply perform periodic backups or archive the real-time database, and then run their queries
on the disconnected-mode (non-real-time) data.
■ a database that has been backed up and repurposed (copied) for running analyses might require a refresh
because the original source database might have had data updated or values changed.
Search JabSto ::