Keeping a OdbcConnection Open

4 Nov 2007 2:44 AM

Paul

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

7 Nov 2007 8:06 AM

Miha Markic

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.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"Paul" <P***@discussions.microsoft.com> wrote in message
news:69022384-7668-439D-BB71-33C8BC49AFE5@microsoft.com...
>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