Thread: slow performance postgres 7.0.3 and odbc
Hello there I just made a test creating 2495 records from a C++ prog. into a postgres table. This took me around 4 minutes. Doing the same against an access db takes 30 seconds ! I use Version 6.50.00.00 from 04.02.2000. What can I do to speed up the ODBC performance? Thanks ... jr ============================================ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ============================================
I think that it is normal that a local data base is faster than a data base client-server I do not believe that the ODBC has a perfomance problem. Regards, Javier Yáñez ----- Original Message ----- From: <juerg.rietmann@pup.ch> To: <pgsql-odbc@postgresql.org> Sent: Friday, June 15, 2001 3:21 PM Subject: [ODBC] slow performance postgres 7.0.3 and odbc > Hello there > > I just made a test creating 2495 records from a C++ prog. into a postgres > table. This took me around 4 minutes. Doing the same against an access db > takes 30 seconds ! I use Version 6.50.00.00 from 04.02.2000. > > What can I do to speed up the ODBC performance? > > Thanks ... jr > > ============================================ > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > ============================================ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
> -----Original Message----- > From: juerg.rietmann@pup.ch [mailto:juerg.rietmann@pup.ch] > Sent: 15 June 2001 14:21 > To: pgsql-odbc@postgresql.org > Subject: [ODBC] slow performance postgres 7.0.3 and odbc > > > Hello there > > I just made a test creating 2495 records from a C++ prog. > into a postgres table. This took me around 4 minutes. Doing > the same against an access db takes 30 seconds ! I use > Version 6.50.00.00 from 04.02.2000. > > What can I do to speed up the ODBC performance? Check that ODBC Tracing is switched off in the ODBC driver manager, and that the Debug Log (mylog) is switched off in the ODBC driver (HKEY_LOCAL_MACHINE\Software\ODBC\odbcinst.ini\PostgreSQL\Debug = 0). The Commlog option uin the driver could slow things as well but not even remotely close to the amount the other two logs can slow the system down. I would also consider getting the latest driver (07.01.0005) from ftp://ftp.postgresql.org/pub/odbc/ HTH, Dave.
perhaps access is faster than postgresql, why you think that the problem is of odbc?, it is equal hardware in both servers? Regards, Javier Yáñez ----- Original Message ----- From: <juerg.rietmann@pup.ch> To: "Javier Yáñez" <javier@cibal.es> Sent: Friday, June 15, 2001 3:42 PM Subject: Re: [ODBC] slow performance postgres 7.0.3 and odbc Hi The access db is also on another server and the update is made over the same network. thanks ... jr
Hi, Unlike access, PostgreSQL (and any other SQL-DB) flushes all writes directly to disk. That makes SQL databases quite safe against power failures. To improve performance database servers need fast hard drives. If you are just creating a new table switching off fscnc() might speed up things considerably. For normal (save) operation you should switch it on again. When I create my database with several tables (with 20.000+ rows), I switch it off. With fsync() off (backend option) you might get results less than 30 sec. So it's not the driver. Hope that helps, regards Johann On Fri, 15 Jun 2001 15:21:16 +0200, juerg.rietmann@pup.ch wrote: >Hello there > >I just made a test creating 2495 records from a C++ prog. into a postgres >table. This took me around 4 minutes. Doing the same against an access db >takes 30 seconds ! I use Version 6.50.00.00 from 04.02.2000. > >What can I do to speed up the ODBC performance? > >Thanks ... jr > >============================================ >PFISTER + PARTNER, SYSTEM - ENGINEERING AG >Juerg Rietmann >Grundstrasse 22a >6343 Rotkreuz >Switzerland > >phone: +4141 790 4040 >fax: +4141 790 2545 >mobile: +4179 211 0315 >============================================ > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly Johann Zuschlag zuschlag2@online.de
Yes, I know, the performace is/was bad. I found a major performace bottleneck in the driver. I fixed it, try the attached ODBC driver. How to: * Backup your register * Run the attached psqlodbc.reg * Rename your current psqlodbc.dll * Copy the attached psqlodbc.dll to the location of renamed psqlodbc.dll * Run much faster! Other tips: * Start postmaster with fsync off * Remove the index and recrate them later * Turn off autocommit and/or begin transaction/commit /Buckis -----Original Message----- From: juerg.rietmann@pup.ch [mailto:juerg.rietmann@pup.ch] Sent: den 15 juni 2001 15:21 To: pgsql-odbc@postgresql.org Subject: [ODBC] slow performance postgres 7.0.3 and odbc Hello there I just made a test creating 2495 records from a C++ prog. into a postgres table. This took me around 4 minutes. Doing the same against an access db takes 30 seconds ! I use Version 6.50.00.00 from 04.02.2000. What can I do to speed up the ODBC performance? Thanks ... jr ============================================ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ============================================ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Attachment
Is this our standard ODBC driver with modifications? Can you send over a conext diff of the source code changes? Thanks. > Yes, I know, the performace is/was bad. I found a major performace > bottleneck in the driver. I fixed it, try the attached ODBC driver. > > How to: > * Backup your register > * Run the attached psqlodbc.reg > * Rename your current psqlodbc.dll > * Copy the attached psqlodbc.dll to the location of renamed psqlodbc.dll > * Run much faster! > > Other tips: > * Start postmaster with fsync off > * Remove the index and recrate them later > * Turn off autocommit and/or begin transaction/commit > > /Buckis > > > > -----Original Message----- > From: juerg.rietmann@pup.ch [mailto:juerg.rietmann@pup.ch] > Sent: den 15 juni 2001 15:21 > To: pgsql-odbc@postgresql.org > Subject: [ODBC] slow performance postgres 7.0.3 and odbc > > > Hello there > > I just made a test creating 2495 records from a C++ prog. into a postgres > table. This took me around 4 minutes. Doing the same against an access db > takes 30 seconds ! I use Version 6.50.00.00 from 04.02.2000. > > What can I do to speed up the ODBC performance? > > Thanks ... jr > > ============================================ > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > ============================================ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi, if I use your version, I get a stack error when reading data. Seems, that your fix is based on an older version off the driver. regards On Fri, 15 Jun 2001 15:54:39 +0200, Christian Bucanac wrote: >Yes, I know, the performace is/was bad. I found a major performace >bottleneck in the driver. I fixed it, try the attached ODBC driver. > <snip> Johann Zuschlag zuschlag2@online.de
I have asked the author for a source patch so we can review it. > Hi, > > if I use your version, I get a stack error when reading data. > Seems, that your fix is based on an older version off the > driver. > > regards > > On Fri, 15 Jun 2001 15:54:39 +0200, Christian Bucanac wrote: > > >Yes, I know, the performace is/was bad. I found a major performace > >bottleneck in the driver. I fixed it, try the attached ODBC driver. > > > <snip> > > > > Johann Zuschlag > zuschlag2@online.de > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026