Skip site navigation (1) Skip section navigation (2)

psqlODBC with Visual Studio 2005 and Connection Pooling for newbies

From: Patrick Donelan <pdonelan(at)optusnet(dot)com(dot)au>
To: pgsql-odbc(at)postgresql(dot)org
Subject: psqlODBC with Visual Studio 2005 and Connection Pooling for newbies
Date: 2005-12-20 00:26:07
Message-ID: 43A74F9F.5050601@optusnet.com.au (view raw or flat)
Thread:
Lists: pgsql-odbc
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

Responses

pgsql-odbc by date

Next:From: Eugene FokinDate: 2005-12-20 07:40:54
Subject: Re: [ psqlodbc-Bugs-1000476 ] DateStyle and Transaction
Previous:From: Ludek FinstrleDate: 2005-12-19 22:06:52
Subject: patch for cleanup protocol

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group