Optimizing Queries

This is the first article in a never ending series of articles on how to optimize your queries. Honestly, this is a topic I’m still learning volumes about. I learned pretty early on the number one rule to getting fast results from a SELECT query.

Limit the results to the minimum you need

If you need to see how many records are in a table don’t use:

SELECT * FROM tableName

Use the COUNT(*) function instead.

SELECT COUNT(*) AS cnt FROM tableName

In large tables you will have more than one row, and more than one column. By using COUNT(*) instead you’ve reduced the number of results returned to 1 row and 1 column. This may not always work for you. What if you need to look at a record to get an idea of the type of data is in that table. Instead of using SELECT *, why not check the first few rows?

SELECT TOP 10 * FROM tableName

You’ve limited your results to the top 10. If your table has hundreds of thousands of rows, you just saved yourself a great deal of time.

If you are looking at only one column of data, why would you want to get every column? It’s only going to waste time. When I am looking at “all the values of a column, I’ll always use:

SELECT DISTINCT columnName FROM tableName

It shows me just what I need to make my decisions.

Finally, consider limiting your queries in the WHERE clause too. When you want just a few records, figure out what WHERE clause you could apply to get just a few results, instead of every row in the table! Speaking of WHERE clauses, I’d like to share with you a list. This lists show you the different comparison operations you can do, in order of their speediness. When you’re writing your logic in a WHERE clause, consult this list, and you’ll be able to write more efficient queries!

  1. =
  2. >, >=, <, <=
  3. LIKE
  4. <>, !=

This is only the beginning. In future posts I’ll share with you how index tuning can help, how locking hints can help, how statistics can help. All of this will teach you how to build better queries. Keep practicing, and always consider your queries as being capable of improvements! Never stay satisfied with the current state of your code… That’ll keep you at the top of your game!

I’m closing as I always do, if you have any questions…send them in, I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *