Home > Articles > Data > SQL

  • Print
  • + Share This


This chapter is about making sure your SQL is as effective as possible from a performance point of view. Optimizers vary a lot, so use this information as a starting point, and remember, there are many other factors that can influence performance.

  • Make sure you understand what indexes you have.

  • Next, find out what tools let you look at the optimizer's choices.

  • Once you know your indexes and have a way of finding out whether or not they are being used in a particular query, check the WHERE clause in problem queries. There are many ways you can structure a WHERE clause to invalidate available queries. Often, you can fix a performance problem by making a simple change.

  • Indexes that cover queries (contain all the information you need for the results) can be very useful. There are some strict rules to follow, however, in both creating and using them.

  • Good joins are critical to relational database performance. Joins have many of the strictures of ordinary WHERE clauses.

  • Sorting always means another pass: get the data, then put it in order. Understand when you may be doing unnecessary sorting (with DISTINCT and UNION) and avoid it.

  • HAVING and WHERE are not the same. WHERE eliminates rows before you form groups. HAVING limits the group results. Doing WHERE work in HAVING can slow performance down.

  • Views are handy, but they may be expensive. Understand the cost of view queries, and create views accordingly.

  • Many systems provide ways to go around the optimizer in index choice. Be cautious when you do this.

Asking Performance Questions

Don't come out of this chapter feeling that you shouldn't use functions or DISTINCT or views. Instead, train yourself to look for the fastest way to achieve what you want.

Start by asking the big questions:

  • Does the query return useful results?

  • Is it lacking anything? Does it bring back extraneous information?

  • Is it still needed? Could it be run less frequently or against smaller amounts of data?

  • Is the sense that this query is "slow" realistic? Why?

Next, get some information on the indexes.

  • Are there indexes on columns frequently used in queries?

  • Is the index on the right column or group of columns?

  • Is the optimizer using the indexes?

  • Are there unused indexes?

If the optimizer is not using existing indexes, find out why.

  • Does the optimizer have up-to-date information on the indexed column's statistics?

  • Does a table scan make more sense than using an index (lots of rows coming back)?

  • Is the query written so that it invalidates an available index (math, functions, conversions on indexed columns)?

  • Is IN used where a range or an equal sign would return the same results?

  • Are the columns in multicolumn indexes in the wrong order—built on fname, lname when queries usually look for lname?

Finally, look for ways you can speed up processing.

  • Would it help to add an index?

  • Could indexes that are never used be dropped?

  • Could common queries benefit from covering indexes?

  • Are there unnecessary DISTINCTs?

  • Does the query use UNION where UNION ALL would work?

  • Are views more complex than necessary?

  • Are hardwired index choices now less than optimal?

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.