Dienstag, 16. März 2010

fastest SQL?

Recently I asked what ist the fastest SQL you can create? on oracle-l mailing list.
It was a kind of challenge. But not a real clever one.
I was more interrested in the way people think and react to this question than in a particular SQL or result.

Here are some summaries for those who are interrested:
There where 33 replies (until now), these can be grouped by some categories (some mails belong to more than one):

select 1 from dual; was suggested by Niall Litchfield and Kevin Lidh.
Don Granaman showed an imprived version with a view on sys.v_dual, but Niall and Norman Dunbar argued, that's what fast_dual was introduced for.

Niall and Wolfgang Breitling asked for better specifications for the quest, which I left open by intention.

Another approach started with exit; by Daniel Fink. Robert Freeman called this not a SQL, but Grant Allen (--) and Jaromir D.B. Nemec ("empty statement") tried the same direction.
Job Miller suggested the qhole quest as a kind of trick question With the answer You look at the statement/business logic and decide that you don't need to run that statement at all. and Daniel gave a real world example for this effect.

Toon Koppelaars and Andre van Winssen both suggested query result cache. I tried to test this but could not find a suficient measurement. Brandon Allen raised the method by checking elapsed_time down to microseconds from v$sql/v$sqlstats.

Wolfgang did one step further with his client side cache.

A discussion about other products like purple and Mauve by Jack C. Applewhite, Christopher Boyle, John Piwowar and Jason Heinrich.

I think y'all need a hobby- and one that doesn't include oracle! by Kellyn Pedersen and Kathy Duret seems to be worth a seperate blog!
Kommentar veröffentlichen