Обсуждение: psqlODBC with Visual Studio 2005 and Connection Pooling for newbies

Hi guys,

I started using psqlODBC with Visual Studio 2005 a few weeks ago to
build a C# app. I've done a non-trivial amount of db stuff before but
not much PostgreSQL and nothing from Visual Studio. I thought I should
share my experience with you guys, because I got really stuck on a
simple problem that almost caused me to scrap psqlODBC altogether.

Once I got all the basics under control and started populating my
database I found my program frustratingly slow, even though I was
running the postgresql server on localhost. I'm using the new
TableAdapter/Dataset stuff that Microsoft introduced with VS 2005, where
it does a lot of the connection management for you and gives you
strongly typed queries etc.. Anyway, I enabled commlog and saw that my
program was disconnecting and reconnecting for every single query,
meaning that 2000 "delete * from mytable" took over 10 mins. I'm sure
this is obvious to you guys but it took me quite a while to figure out
that my program wasn't using Connection Pooling, and longer still to
work out that I had to go to the Data Sources (ODBC) control panel in
Administrative Tools to enable Connection Pooling for the PostgreSQL
ANSI/Unicode drivers. After I did that my 2000 queries took about 10
seconds (massive speedup), and I could see in the commlog that my
program was executing the queries immediately after each other without
disconnecting. Still 2~3 times slower than executing the queries
directly with npgsql (I assume because of the overhead created by the
TableAdapter stuff) but good enough for my program.

Anyway, as far as I can tell the driver is installed by default with
Connection Pooling off which means that most newbies like me are going
to hit the performance problem and think that something is wrong with
psqlODBC. I thought it might be worth putting a note in the README
alerting people to this.

At this stage I'm thinking of having my app forcibly turn Connection
Pooling on by setting the Windows Registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL Unicode
CPTimeout = 60

Is this the way to go? Or is there a way that I can enable it for my
program only with the Connection String? The Visual Studio help files
seemed to indicate that for ODBC you can only set it on a system-wide basis.

Anyway, thanks for the great driver guys. Keep up the good work!

Patrick

P.S. One other note while I'm writing, in
FAQ 3.3) What do I need to do to establish a connection to a database?
the second-last item in the checklist says:
Postmaster must run with the -i option , or tcpip=true in
postgresql.conf to allow remote connections.

This should be updated to the statement in the PostgreSQL 8.1.0
Documentation:
Add tcpip = true to the postgresql.conf file for Versions 7.3.x and
7.4.x, or listen_addresses='*' for Version 8.0.x and above

> Anyway, as far as I can tell the driver is installed by default with
> Connection Pooling off which means that most newbies like me are going
> to hit the performance problem and think that something is wrong with
> psqlODBC. I thought it might be worth putting a note in the README
> alerting people to this.

I think we can change the default behaviour.

> At this stage I'm thinking of having my app forcibly turn Connection
> Pooling on by setting the Windows Registry key:
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL Unicode
> CPTimeout = 60

Dave, it's good idea to add this to .reg file or install procedure,
isn't it?  Please could you add it before releasing 08.01.0200?

> Is this the way to go? Or is there a way that I can enable it for my
> program only with the Connection String? The Visual Studio help files
> seemed to indicate that for ODBC you can only set it on a system-wide basis.

I don't know exactly but I think this is directive only for ODBC manager
not for psqlODBC driver.

> P.S. One other note while I'm writing, in
> FAQ 3.3) What do I need to do to establish a connection to a database?

Could you send us patch for this issue?

Thanks,

Luf

On 21/12/05 1:24 pm, "Ludek Finstrle" <luf@pzkagis.cz> wrote:

>> Anyway, as far as I can tell the driver is installed by default with
>> Connection Pooling off which means that most newbies like me are going
>> to hit the performance problem and think that something is wrong with
>> psqlODBC. I thought it might be worth putting a note in the README
>> alerting people to this.
>
> I think we can change the default behaviour.
>
>> At this stage I'm thinking of having my app forcibly turn Connection
>> Pooling on by setting the Windows Registry key:
>> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL Unicode
>> CPTimeout = 60
>
> Dave, it's good idea to add this to .reg file or install procedure,
> isn't it?  Please could you add it before releasing 08.01.0200?

I don't think so, because it is only safe to use if you know that the
application isn't going to execute any SET's or otherwise change the
connection environment before returning the connection to the pool. The DM
pools connections based entirely on the connection string, and uid/pwd, and
connection-specific server settings.

To be safe, when the app calls SQLDisconnect it seems to me that all
temporary tables must be dropped, and a RESET ALL executed. I'm not sure how
to achieve this though given that the DM presumably intercepts the
SQLDisconnect call when pooling.

If it doesn't intercept the call (which seems unlikely), then CC_cleanup
will do a pretty good job of making the connection unusable anyway.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/od
bcodbc_connection_pooling.asp

Regards, Dave