More advanced SQL analysis

So the basics of analyzing SQL on db2 are in my post on Analyzing SQL. I wanted to go a bit beyond the basics.

First, you have to be able to read an explain plan. A couple of resources on that:

http://web.archive.org/web/20040603042937/http://www.idug.org/idug/member/journal/Nov01/articl05.cfm

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005134.html

I’m not doing to describe reading explain plans at this time, but it is absolutely critical knowledge for a DB2 dba.

Analyzing custom SQL for db2 databases – for WebSphere Commerce OR other applications

So after you have the db2advis and db2exfmt output, you can use that to play around with the SQL. You can use the explain plan to find the biggest consumers of timerons, and see if there’s anything you can do in the SQL to eliminate or change the way that section is happening. The most obvious here is the addition of an index to eliminate a table scan, but eliminating an expensive sort or index scan is just as valid.

While you won’t always get much performance out of re-writing it, sometimes you really can. Some things to try:

Converting a distinct to a group by
Eliminating sub-selects to incorporate them with the main query
If the work can be done at the application level instead, eliminating “order by”‘s
Eliminating IN lists in favor of =
Eliminating “select *” in subqueries for “where exists” – “select 1” works just as well, and may return less data
Adding “with ur” on the end of queries if it will work for the application
Ensuring joins have the appropriate predicates to prevent a Cartesian Product
Analyze use of temporary tables, both those declared and anonymous temp tables

When you’ve changed the syntax, always make sure you get the right results, and then do the exact same analysis you originally did – generating the explain plan and db2advis output, and compare the number of timerons between the altered sql and the old SQL. Depending on the statement, I might try 4 or 5 different alterations before giving up.

It is important as you work through to track what you’re trying. I use a naming system for my sql files on the server to indicate alternate syntaxes tried, and most of the time also have a spreadsheet. The spreadsheet helps me have a place to note random comments or to go back and add more comments later if something I’m looking at later makes me think of something valid for a previously analyzed query.

Analyzing custom SQL for WebSphere Commerce databases when you have no access to the databas in question

So sometimes I get requests for SQL analysis where I don’t actually have access to the database or even a test version of it. This is obviously difficult because I can’t run explains and such. Some of these databases are even <gasp!> Oracle. So what I do is indicate to the requester that I can’t do a full analysis, but I can do a basic sanity check. Here’s what I do:

Basic look at the SQL for poorer performers where better performers are available – discussion with the developers about the basics of efficient SQL
List all the tables accessed by each SQL statement. Note multiple accesses to the same table (which may be needed, but still).
List all columns used in joins and then use the Commerce info center to make sure that all join columns are the first column in an index. Report join columns without an appropriate index to the requester.
Look for the comparison of unlike-data types. Oracle may support direct comparisons, but they perform poorly. DB2 requires the use of a function, but the use of such functions is less than optimal
Look for oddities like “where 1=1” or other things that developers sometimes use or copy
Look for and report to the developers any queries that appear at first glance to return large result sets – such as ones that would return most of the orders table or most of the members table, etc – ask the developers if they’re necessary or if a smaller subset of the data is what is really being used. This same thing holds true for any queries that return * or a large number of columns.
While reviewing the SQL, keep your eyes open for syntax mistakes that may lead to unexpected joins or other issues.

Obviously if you can take the SQL and do a full analysis through the explain plans and DB2 Design Advisor, that’s preferred, but in some cases, that may not be possible.