Thread: 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