Thread: slow performance postgres 7.0.3 and odbc

slow performance postgres 7.0.3 and odbc

From
juerg.rietmann@pup.ch
Date:
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
============================================


Re: slow performance postgres 7.0.3 and odbc

From
Javier Yáñez
Date:
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
>
>



RE: slow performance postgres 7.0.3 and odbc

From
Dave Page
Date:

> -----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.

Re: slow performance postgres 7.0.3 and odbc

From
Javier Yáñez
Date:
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






Re: slow performance postgres 7.0.3 and odbc

From
"Johann Zuschlag"
Date:
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



RE: slow performance postgres 7.0.3 and odbc

From
Christian Bucanac
Date:
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

Re: slow performance postgres 7.0.3 and odbc

From
Bruce Momjian
Date:
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

RE: slow performance postgres 7.0.3 and odbc

From
"Johann Zuschlag"
Date:
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



Re: slow performance postgres 7.0.3 and odbc

From
Bruce Momjian
Date:
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