Archives of the TeradataForum
Message Posted: Mon, 16 Jun 2003 @ 20:56:53 GMT
| Subj: || || Query Explain Summary Tool (MS Access 2K) |
| From: || || Terry Stover |
I wrote an MS Access app to facilitate Teradata query tuning by presenting a total query summary of the key statistics from the explain
(no more adding up the times from each step). The app parses the text from a Teradata query explain and summarizes the total query time and
the number of joins by join type. It also has a report that has a more user friendly presentation of the explain output.
I didn't find the Teradata query explain capability to be very friendly for query tuning. I tried using the visual explain tool, but it
was buggy, took way too long and consumed a lot of disk space. All I wanted was the information on the visual explain summary tab: the
total time and the number and types of joins. When I am tuning queries I just need the net impact on time to quickly assess whether a sql
change has a measurable impact on execution time. The NCR tools give you lots of details, but you have to manually add up the values from
each step. This application runs the explain in a pass-thru query, parses the results and calculates the relevant totals. It's been tested
pretty thoroughly with 4.1.0 and 4.1.3 and has had limited testing on 5.0.1. Disclaimer: it relies on string parsing any change to the
explain output can break the app. Currently it only works with Access 2000 or 2002 (access 97 doesn't have a replace function). It's 95K
zipped, 365K unzipped, give it a try.