When Query Speed Counts

Stopwatch

There are often numerous ways to write a particular query or stored procedure. In some cases, the approach makes little difference. Other times it can lead to poor performance that wreaks havoc on an application or service. Taking care as you go will help to avoid slowdowns and prevent the need for rework when query speed counts. The following suggestions can also be used for troubleshooting existing pain points or new ones that pop up from changes or growth within your database. 

Not every suggestion is appropriate (or possible) for every scenario.

Things to Consider
  • Review relationships and indexes. Pay particular attention to primary and frequently used columns, as well as what’s in your Where clauses. Use caution here, though, (or consult a DBA) as bad indexes can be worse than no indexes. 
  • Use Inner Joins unless you have a specific reason not to do so. If you aren’t familiar with the tables involved be sure to test to ensure you won’t be losing relevant data.
  • Use a join or two to quickly limit scope before getting into the nuances of the query. The fewer records you have after the first line or two of code, the faster the query will accomplish getting other related details from subsequent tables you join. It’s not always best to leave filtering for the Where clause.
  • Use a temp table to isolate key records. Suppose the starting table you’re targeting has a couple hundred columns and contains millions of transactions. You ultimately need to join several other tables. Start by writing a query to select just the primary key value from the starting table for relevant records and insert these values into a temp table, adding an index. Then, start a new Select from the temp table, joining to other tables as needed. This can drastically improve runtime. *Using temp tables over table variables is almost always faster but you may need to try/compare if the temp table isn’t getting the job done.
  • Don’t nest Left or Right Joins, it will kill performance. Instead consider using a temp table to capture content from just those nested joins. You can then use a single Left or Right Join in a subsequent query back to the temp table.

Do you have other go to tips & tricks?

If you’d like to learn more or have BTG assist with an upcoming project, please contact us at info@bluetridentgroup.com or book a free consultation here.