Let's say I have a giant SELECT buried inside an application, and I just want to do a quick dbisql timing test without all the application complexity. Here's the pseudo code for an example
SELECT blah blah blah FROM blah blah blah WHERE blah blah blah AND SO ON;
All else being equal (same predicates, same host variable values, same everything else except the SELECT list), can I rely on the following being just as slow as the original?
Let's further assume there is no funkiness in the SELECT list, no user-defined function calls that do RECURSIVE CROSS JOIN UPDATEs between DBA.Mars and DBA.TheMoon, none of that stuff, just ordinary columns.
SELECT COUNT(*) FROM blah blah blah WHERE blah blah blah AND SO ON;
asked 22 Jul '11, 05:41
There are a number of reasons the
I know that you suggested there are no UDFs in the SELECT list (those would not need to be evaluated in the
There are a couple of techniques that I use (with their own limitations). You can use a row-limitation clause such as
Another approach I have recently become fond of is to use the
Comparing the techniques, the TOP-n/START AT approach is closest to the
All of these approaches intentionally eliminate the network time needed to send the result back. In the cases where that is pertinent, the fetchtst tool is a great way to test the performance of a query; it allows you to control the cursor type, it supports host variables, and it can simulate different ways of fetching the result set. It can also be combined with the above described techniques.
answered 22 Jul '11, 06:52
Ivan T. Bowman