Optimizing searches in an OLTP database

Find out why Arthur Fuller decided to throw 20 years of assumptions about data singularity out the window when he discovered the effectiveness of optimizing searches in an OLTP database. Based on his experiments, he notes that the performance effects of using this approach are profound.

While eating dinner recently with Dejan Sunderic (author of SQL Server 2000 Stored Procedure & XML Programming), he made me rethink some of my long-held biases about data singularity. His well-made points spurred me to write this article.

Assume that you have an OLTP database that uses several tables to populate a list consumed by the front end, which involves a dozen joins and resolves the foreign keys, retrieving the name of the province, city, state, and so on.

If you direct the retrieval to the actual tables by using the required joins, the inevitable result is that you slow down the data entry side of the operation. Since data entry essentially reduces to income generated, this is a problem.

Instead, create de-normalized tables used strictly for such lookups. No joins are required and no penalty is imposed on the performance of the data entry aspect of the application.

How would you handle the situation if a user adds a row to one of the tables whose data ends up in your de-normalized table? I think that triggers are the perfect solution.

Now suppose that Query1 involves n tables in various joins. This query can just as easily become a delete/insert query (i.e., you delete all the existing rows and insert all the new ones from the query). That would always be correct but, most often, it would be inefficient. You should rewrite the query as a User-Defined Function (UDF) so you can pass parameters to it and receive a table as the result. Then, you can update or insert rows as appropriate.

Here's how to rewrite the query: Take your retrieval query that joins n tables and turn the result into an actual table. Then, write some triggers that fix up this table when required. Try it on some database you have that queries, say, a dozen tables in various joins. Make a results table from that and query that instead. Depending upon the table sizes, the difference can vary from obvious to dramatic.

By placing such code in a trigger, you guarantee its success. The lookup table is corrected, whether by deleting, inserting, or updating a row. The next time the lookup table is polled, the change will appear. This part is simply the error-handling.

The important thing about this concept is that a de-normalized table handles the lookups. This means that no locks are imposed on rows in the transaction tables. I experimented with one of my databases and discovered that the effects are profound—especially if you're dealing with 100,000 or more rows.

From my enlightening dinner conversation, I learned to question my favorite assumptions. For 20 years, my assumptions about data singularity have blinded me to the effectiveness of this approach.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks