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

[Colin Williams <colin.williams@xxxxxxxxxxxx>]

Walden,

"Select * from qsqptabl"

Qsqptabl is a file in qsys2 with 1 record with 1 field in it

cheers
Colin.W
 


-----Original Message-----
From: Walden H. Leverich [mailto:WaldenL@xxxxxxxxxxxxxxx] 
Sent: 17 November 2004 17:30
To: Midrange Systems Technical Discussion
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
 
PS. Do I understand the connection pool in WebSphere correctly?

------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com <http://www.techsoftinc.com/> 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
  

 
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail has been sent by a company of Bertram Group Ltd, whose registered 
office is 1 Broadland Business Park, Norwich, NR7 0WF. 
This message, and any attachments, are intended solely for the addressee and 
may contain privileged or confidential information.  If you are not the 
intended recipient, any disclosure, copying, distribution or any action taken 
or omitted to be taken in reliance on it, is prohibited and may be unlawful.  
If you believe that you have received this email in error, please contact the 
sender immediately. Opinions, conclusions and statements of intent in this 
e-mail are those of the sender and will not bind a Bertram Group Ltd company 
unless confirmed in writing by a director independently of this message. 
Although we have taken steps to ensure that this email and any attachments are 
free from any virus, we advise that in keeping with good computing practice the 
recipient should ensure they are actually virus free.