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

David,

As I'm sure you're aware, creating a connection is one of the most
expensive database operations you can do. Think about what has to happen
to connect to the iSeries: you create a connection, it has to find the
iSeries, open a tcpip connection to it, the iSeries has to create a job,
authenticate, and start listening for requests. Some of this time is
lessened by the use of prestart jobs on the iSeries, but it's still not
a fast operation. Ideally you won't create connections often.

Connection pooling in .NET is rather transparent. You create a new
connection object and when you're done with it you close it, normal
stuff. However, behind the scenes ADO.NET (and most native .NET
providers, like Sql, Oracle, iSeries, etc.) holds onto that connection
and doesn't actually close it. Then, when you next "create" a
connection, the system says "have I got a deal for you" and hands you
back the old connection it held on to. You can only pool connections
that use the same connection string, so you don't have to worry about
sharing different user ids, for example. 

Here's the problem for some of the providers the pool simply holds onto
the connection and hands it back, there's no checking that the
connection is still valid. Nor is there any way to expire a connection,
say after 100 uses. Since each connection has a job on the iSeries what
would happen if someone ended the job on the iSeries? The connection
would still be sitting in the pool on the pc and when your code tried to
use it, boom!

I'm looking to implement a smarter connection pool. This connection pool
would allow more control over the connection's lifespan, and would check
that the connection was still valid before handing it out -- by running
that quick SQL statement. Additionally, I would like to limit the number
of connections and hand them out by priority, high, medium and low. So
you could setup your system and say, I want a minimum of 3 connections
and a maximum of 30 connections to my iSeries. I want to save 3 of those
connections for "high priority" requests. Then if 30 connections were in
use and a thread requested the 31st, it would simply wait for an
available connection. 

This is still and idea of mine, nothing more, so I'm sure there are all
sorts of problems to be worked out.

-Walden

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

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
 
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Dave
Sent: Wednesday, 17 November, 2004 14:33
To: midrange-l@xxxxxxxxxxxx
Subject: Fastest SQL Statement -- doesn't need to do anything.

Walden,

Please shed some light on connection pooling for me hopefully specific
to .net.   I think I understand the concept but where does the
configuration come into play.   Is connection pooling defined (the use
and the time out limit) on the IIS server?, on the individual users PC
ODBC connection?, in the connection string used in the .net language?
I'm confused.

David Smith
iSeries Consultant
dsmith@xxxxxxxxxx
dsmith@xxxxxxxxx

 
                >use <database>

                USE seems to blow in STRSQL, but might be allowed in SQL
from the PC.
                However, it got me thinking. CONNECT is a good one.
Cool, no errors and
                no IO! 

                -Walden

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