pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad performance using ODBC
3+ messages / 2 participants
[nested] [flat]

* Bad performance using ODBC
@ 2003-07-16 14:15 Albert Cervera Areny <albertca@jazzfree.com>
  2003-07-16 14:38 ` Re: [PERFORM] Bad performance using ODBC Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Albert Cervera Areny @ 2003-07-16 14:15 UTC (permalink / raw)
  To: pgsql-odbc@postgresql.org; pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,
  I have a performance problem using postgresql when the connection is made 
via ODBC with a windows machine using the latests ODBC drivers (Windows) and 
PostgreSQL 7.3.3 (Linux).

  The queries made by my Visual Basic program are very very simple. It queries 
with as Select if a record exists and if so, it reduces stock with an Update. 
For the benchmarks I do it 200 times.

  If I test it against an Access database (located in a SMB server) it spends 
3 seconds but against PostgreSQL 17 !! Exactly the same test programmed in C 
(with pgsql libraries) and run within the same machine or another Linux 
spends less than a second!!

  So the problem seems to be whether with the ODBC drivers or with Windows 
ODBC itself. Are there any parameters in the ODBC drivers that might help 
reducing that big overhead added or do you have any suggestions to speed it 
up?

Thanks in advance!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/FV3pTK7ZP4pDOHcRAmVWAJ9KF/YyKmuBZcidV3FK2gESaX25NwCgjABx
6WhA0HgC7oxF7VFJeczIrgE=
=3H+u
-----END PGP SIGNATURE-----



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [PERFORM] Bad performance using ODBC
  2003-07-16 14:15 Bad performance using ODBC Albert Cervera Areny <albertca@jazzfree.com>
@ 2003-07-16 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-17 16:14   ` Re: [PERFORM] Bad performance using ODBC Albert Cervera Areny <albertca@jazzfree.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2003-07-16 14:38 UTC (permalink / raw)
  To: albertca@jazzfree.com; +Cc: pgsql-odbc@postgresql.org; pgsql-performance

Albert Cervera Areny <albertca@jazzfree.com> writes:
>   I have a performance problem using postgresql when the connection is made 
> via ODBC with a windows machine using the latests ODBC drivers (Windows) and 
> PostgreSQL 7.3.3 (Linux).

Do you have logging turned on in the ODBC driver?  I recall hearing that
that adds a heck of a lot of overhead...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [PERFORM] Bad performance using ODBC
  2003-07-16 14:15 Bad performance using ODBC Albert Cervera Areny <albertca@jazzfree.com>
  2003-07-16 14:38 ` Re: [PERFORM] Bad performance using ODBC Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-17 16:14   ` Albert Cervera Areny <albertca@jazzfree.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Albert Cervera Areny @ 2003-07-17 16:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-odbc@postgresql.org; pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A Dimecres 16 Juliol 2003 16:38, Tom Lane va escriure:
> Albert Cervera Areny <albertca@jazzfree.com> writes:
> >   I have a performance problem using postgresql when the connection is
> > made via ODBC with a windows machine using the latests ODBC drivers
> > (Windows) and PostgreSQL 7.3.3 (Linux).
>
> Do you have logging turned on in the ODBC driver?  I recall hearing that
> that adds a heck of a lot of overhead...

After trying too many things I've finally been able to make it run in just 1 
or 2 seconds. I simply had to change the recordset type and set it to 
dbOpenSnapshot (This one doesn't show changes made to the database once it's 
been open) instead of the default dbDynaset (much more powerful but 
unnecessary in this application).

Take note that though it might seem obvious the performance loss against 
Access isn't that much and thus VB users aren't probably used to change the 
recordset type. I think It would be nice a note with this performance 
benchmarks (2 seconds against 15) in the Mini-Howto on Accessing PostgreSQL 
from Visual Basic. I'll contact Dave Page directly in case he finds it 
interesting.

I haven't seen any speed improvements desabling logging but thanks for your 
suggestion anyway!

>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/FstfTK7ZP4pDOHcRArepAJ9rIhOKtztuPORbGkrVTOfC4UmUOQCeJ00u
UxJegkvrs4TL3QVXNun3iFs=
=itG7
-----END PGP SIGNATURE-----



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-07-17 16:14 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-16 14:15 Bad performance using ODBC Albert Cervera Areny <albertca@jazzfree.com>
2003-07-16 14:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-17 16:14   ` Albert Cervera Areny <albertca@jazzfree.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox