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; |
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. 3
You should write a book. I know, you have real work to do, but still, you should do it.
(22 Jul '11, 14:38)
Breck Carter
Replies hidden
Well, a blog would do, too ... I guess I remember you sighing "If Ivan had a blog" once upon a time in your blog, Breck:)
(22 Jul '11, 17:26)
Volker Barth
2
Thank you @Breck and @Volker; I appreciate your kind comments (and Breck's earlier suggestion that I start a blog). I do have a blog (actually two) that are both for limited consumption. I haven't found that I update either with any appropriate frequency. My non-work blog was last updated six years ago, my "work internal" blog over 2 years ago. If I could achieve a better frequency, I'd consider having a public work-related blog.
(25 Jul '11, 19:21)
Ivan T. Bowman
Replies hidden
It gets easier with increased frequency... and nobody's gonna complain if you do a bit of self-plagiary, like edit and republish the detailed answers you have posted here and on the newsgroups. Or not... leave them for ME to republish :)
(26 Jul '11, 08:32)
Breck Carter
|
What about
Unfortunately (for Breck in this case; fortunately for other SA users the rest of the time), the optimizer is likely to prune this query down until the intermediate select list is empty, at which point it is no different from Breck's original count(*) question.