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

["John Brandt Sr." <pgmr@xxxxxxxxxxxxxxx>]

use <database>

John Brandt 
iStudio400.com 
(903) 523-0708 
Home of iS/ODBC - MSSQL access from iSeries and RPG. 




-----Original Message-----
From: Walden H. Leverich [mailto:WaldenL@xxxxxxxxxxxxxxx]
Sent: Wednesday, November 17, 2004 11:30 AM
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.

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.792 / Virus Database: 536 - Release Date: 11/9/04
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.797 / Virus Database: 541 - Release Date: 11/15/04