Browse DevX
Sign up for e-mail newsletters from DevX


DB2 Explains Itself: A Roadmap to Faster Query Runtime-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Reducing the Cost
Now that you can read the price tags, it's time to reduce the cost. Here are some guidelines:

1.) Better-informed decisions. DB2's optimizer must be well informed to make the best decisions.

  • Use the RUNSTATS to update the statistics. If your data has grown, or you've added or removed indexes since the last RUNSTATS, DB2's estimates will be inaccurate.
  • If your tables aren't fully populated, the runtime statistics will be inappropriate. Under such conditions you'll often find tablescans in your plans, in spite of sensible indexes. You can tell the optimizer to anticipate extra data by marking certain tables as VOLATILE. (Use the Control Center, or the ALTER TABLE command).

2.) Don't buy junk. It's a simple rule - don't buy what you don't need. This includes:

  • Don't use "SELECT * " when you don't need all the columns.
  • Use a WHERE clause when possible. A common beginner's mistake is to use HAVING instead of WHERE. "HAVING" is like analyzing every item in a supermarket, then discarding those you don't want. Sometimes that's what you need, but it's quicker if you can say "I only want to look at the breakfast cereals."
  • If you only want the first row(s) of a large result set, use FETCH FIRST (see my previous solution "Mastering Top-N and OLAP Queries in DB2").
  • Look carefully at your joins. The sooner (in the plan) you can narrow down the records you want, the less work it will be. Compare Listing 3 and Listing 4. They represent the same query, but by re-ordering the joins, the cost has been halved. (The reason for the cost difference relates to how many rows are in each table).

3.) Keep an index handy. Many columns can be used in an index, and the order of the columns is significant. You might want to use more than one index.

  • DB2 provides a clever tool, the SQL Advise Facility. It's available from the Control Center (Indexes - Create - Index using wizard), and from the command line as db2advis. The advisor takes a query, examines the generated plan, attempts to identify additional indexes that may help, and re-evaluates the cost with and without each of the indexes. The end result is a list of potentially helpful indexes, with estimates of their size and helpfulness.
  • You can also INCLUDE[] unsorted data in an index, to completely avoid fetching from the table in some instances.

4.) Use a Summary Table. See my solution "Tame Beastly Data with Summary Tables."

While there's much more that can be learned on your way to writing efficient queries, you've got a good start in knowing how to measure the impact of your efforts.

Greg Nash is an integration engineer with a manufacturing company.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date