Brian Davignon

Okay, so you've used showplan, statistics i/o and statistics time to identify how your query has been optimized and what it's costing you. That's a good starting point. Sometimes all the answers are right there in the query plan or resource counts. They'll point out a table scan, the fact that your query is using a different index than you expected or that most of your I/O relates to one particular table or SQL statement. You know you have an index on the search columns, so why did it choose a tablescan? You've done your homework and identified that one index is more selective than another, yet the optimizer decided to access the table with the less selective index. You suspect that one join order is better than another, based on your knowledge of the data and relationships, but it didn't choose that join order. Unfortunately, these tools won't help you in these sit... (more)

Showplan Basics, Optimize your SQL

As developers, we often focus on code and give less attention to the database than we should. Developers on large development teams, who can usually rely on the database programmers and/or DBA to write and optimize their SQL, tend to shy away from anything database related. On smaller teams the developers are responsible for the schema and DML, but typically don't understand the nuances and complexities of the specific database they're working with. A very important yet undervalued feature in PowerBuilder's database painter, "Explain SQL," takes advantage of the database engine's... (more)

Beyond Showplan

Okay, so you've used showplan, statistics i/o and statistics time to identify how your query has been optimized and what it's costing you. That's a good starting point. Sometimes all the answers are right there in the query plan or resource counts. They'll point out a table scan, the fact that your query is using a different index than you expected or that most of your I/O relates to one particular table or SQL statement. You know you have an index on the search columns, so why did it choose a tablescan? You've done your homework and identified that one index is more selective th... (more)