Keeping a OdbcConnection Open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am dealing with an AS400 DB2 system and am running into some query
execution timeouts. It seems like every time you Open the initial connection
the query can take over 1 min to return results. Once the connection is open
the next queries execute as expected, usually.

If I leave the connection open and reuse it for each call does it behave
asyncronously and execute any query as it comes or is it sequentail and will
process them one at a time. So If I had a query that took 2 mins to execute
does everthing stand still until it is complete?

I have 100+ users using an application that queries for account info and
does stuff with the results.

If I use the typical scenerio, Open then Close for each request I get random
execution times, milliseconds then around 1 min (For the same query)

I dont necessarily agree with keeping the connection open but was wondering
if anyone out there has come across this situation and came up with some
different solutions.

Thanks for your time
- Paul
 
Hi Paul,

A basic rule is that connection isn't thread safe - it executes commands
sequentially.
So you'll need a connection per command if more than one command is run at
same time.
That leads us to connection pooling which opens as much (physical)
connections as they are needed - unless more than one command at same time
is executed you'll work with one physical connection. If more than one
commands is to be executed at same time, more physical connections are
created and after commands are executed are (or are not) disposed. The
conneciton pool behavious in Odbc case is defined by ODBC Driver Manager
(see Understanding Connection Pooling help topic).
So, using connection pooling, open connection right before it is needed and
disposing it asap, is the right way.
 
Back
Top