Thinking Outside the Box - Help Writing Faster Queries - Part 3 of 3

Hello and welcome to our blog!

This is part three of our series to help you write faster queries.

In part one you learned about the power of indexes, when an index should be used and how to create an index.

In part two you learned about the performance hits associated with using function calls in your Where clauses.

Now, in part three, let’s think outside the box a bit. I know I said I was going to give tips on how to make your query faster… and I am. However, this may be a bit off the beaten path. But doesn’t that make for a great tip? Something you haven’t already heard a hundred times?


Help Writing Faster SQL Queries
Part 3 : Thinking Outside the Box!

Here’s the backdrop: The app is in the can, working great, and the roll out begins. Within a few days, you start hearing murmurs that parts of the app are slow. These murmurings soon become help tickets. These help tickets soon become meetings with the boss and the users. These meeting often turn into alcoholism…. You get the point.

This unfortunate situation arises all too frequently, and there is a relatively simple solution to it.

You see, everything always performs well in your development environment because you have relatively few records in your development database! Sure, if I only had 30 records in my main table, my application will work wonderfully too! Once it gets into the real world, and a few hundred thousand or millions of records are input or imported, the need for performance tuning becomes a critical apparentness.

The solution is simple; create the same number of records in your development environment, as you’d expect to have in the first year of operation. If you have importable data (say, from legacy systems or third party feeds) them pump them in. If you do not, then there are many open source utilities to create mock data that would closely conform to your actual data. If you are the creative type you may write your own code to populate the tables with "dummy data". Regardless of how you fill the tables, the point is that now, as your first writing your queries, you’ll have a really good idea if you need to concentrate on a particular query to avoid future headaches and counseling bills.

Another thing you may wish to do is model the effect of concurrent users in your database system. One easy way to do this is to create a script that simply performs searches on a table (or across multiple tables joined together). In addition, you can create another script to insert records in a table and a third script to update records.

Schedule these scripts to run every minute throughout the day and night. Doing this will result in 60 "dummy records" being inserted each hour (1,440 records added each day). Of course, you should tweak the time based on the amount of transactions you expect your system to have "in the real world". You may want to add one record per hour or you may want to add 100 records every minute.

Now, when developing your database storage and retrieval systems you will not be "alone in the box". These automated processes will simulate some of the load you can expect to deal with on your Production environment when users are searching for information, entering information and updating information. This can go a long way to help you identify areas vulnerable to contention.

This concludes our series providing help writing faster sql queries.

However, this certainly is not the end of the information we intend to share!

Please bookmark this blog now so you can easily come back tomorrow and learn more great SQL programming tips and tricks.

The SQL Coach Team