RE: Fastest SQL Statement -- doesn't need to do anything.

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

I've seen ERP apps do SELECT * FROM something/something WHERE 1 = 0 and
never really understood why in the world would they do that.  
Reading your post and what your intent is, I think I may finally have an
answer to that 'nonsense' statement.

Elvis

-----Original Message-----
Subject: Fastest SQL Statement -- doesn't need to do anything.

All,
 
What would be the fastest possible SQL statement, in terms of execution
time?
 
I'm asking from the point of view of connection pooling. From what I
know of WebSpehere (and that is NOT much <G>) there is an option in the
connection pool to execute a query on a connection before handing it
out, thus checking that the connection is still valid. I like this and
I'm looking at creating the same idea in a .NET environment.
 
I need the SQL statement to run that is as fast as possible, and I don't
care about the results, I just want to make sure that the iSeries on the
other end of that connection is still there. I'm of two minds here, the
first is to have a single column single row table and select * from it,
the second is to execute some invalid SQL operation like "n". 
 
In the first case this will happen so often that the table is likely to
always be in memory so there's not likely to be any physical IO
involved, but there is still logical IO, and all the code necessary to
build and return the result (even if it's empty). 
 
In the second case there'd be zero IO since the request wouldn't get
past the SQL validate step, but there would be error handling. 
 
I know I could just try each 1,000,000 times and time them, but I'm
curious about other options. I'd love a NO-OP command in SQL, something
that said "do nothing" but I'm not aware of one. 
 
Ideas?
 
-Walden