pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIs ODBC that slow?
10+ messages / 4 participants
[nested] [flat]
* Is ODBC that slow?
@ 2006-10-20 22:19 Carlo Stonebanks <stonec.register@sympatico.ca>
0 siblings, 2 replies; 10+ messages in thread
From: Carlo Stonebanks @ 2006-10-20 22:19 UTC (permalink / raw)
To: pgsql-performance
Our Windows-based db server has to integrate with users that work regularily
with Access.When attempting to import user's data from Access MDB files to
PostgreSQL, we try on eof two things: either import using EMS SQL Manager's
Data Import from Access utility, or export from Access to Postgresql via an
odbc-based connectionin both cases, the performance is just awful.
Performance with Tcl's native postgres driver seems rather fine running from
Windows a Windows client, BTW.
ODBC is often blamed for this sort of thing - I have the 8.01.02 release
dated 2006.01.31. Everything appears to be at its default setting.
Is this the reason for the rather depressing performance fromt/to access and
can anything be done about it?
Carlo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 01:04 Merlin Moncure <mmoncure@gmail.com>
parent: Carlo Stonebanks <stonec.register@sympatico.ca>
1 sibling, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2006-10-21 01:04 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
On 10/21/06, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> Our Windows-based db server has to integrate with users that work regularily
> with Access.When attempting to import user's data from Access MDB files to
> PostgreSQL, we try on eof two things: either import using EMS SQL Manager's
> Data Import from Access utility, or export from Access to Postgresql via an
> odbc-based connectionin both cases, the performance is just awful.
> Performance with Tcl's native postgres driver seems rather fine running from
> Windows a Windows client, BTW.
>
> ODBC is often blamed for this sort of thing - I have the 8.01.02 release
> dated 2006.01.31. Everything appears to be at its default setting.
>
> Is this the reason for the rather depressing performance fromt/to access and
> can anything be done about it?
i suspect the problem might be access...the odbc driver now uses libpq
library over postgresql. first thing to do is to monitor what hundred
sql statements access decides to write when you want to, say, look up
a record. the results might suprise you! one gotcha that pops up now
and then is that odbc clients somtimes experience wierd delays in
certain configurations. afaik this has never been solved.
1. turn on full statement logging (log_statement='all'). i prefer to
redirect everything to pg_log and rotate daily, with a month or so of
log files going back. turning on log_duration helps.
2. tail the log, do random things in access and watch the fireworks.
if nothing odd is really going on, then you may have an odbc issue.
more than likely though, access is generating wacky sql. solution in
this case is to code around that in access.
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 02:32 Alvaro Herrera <alvherre@commandprompt.com>
parent: Carlo Stonebanks <stonec.register@sympatico.ca>
1 sibling, 2 replies; 10+ messages in thread
From: Alvaro Herrera @ 2006-10-21 02:32 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; +Cc: pgsql-performance
Carlo Stonebanks wrote:
> Our Windows-based db server has to integrate with users that work regularily
> with Access.When attempting to import user's data from Access MDB files to
> PostgreSQL, we try on eof two things: either import using EMS SQL Manager's
> Data Import from Access utility, or export from Access to Postgresql via an
> odbc-based connectionin both cases, the performance is just awful.
> Performance with Tcl's native postgres driver seems rather fine running from
> Windows a Windows client, BTW.
>
> ODBC is often blamed for this sort of thing - I have the 8.01.02 release
> dated 2006.01.31. Everything appears to be at its default setting.
Try Command Prompt's ODBC driver. Lately it has been measured to be
consistently faster than psqlODBC.
http://projects.commandprompt.com/public/odbcng
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 02:50 Merlin Moncure <mmoncure@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 4 replies; 10+ messages in thread
From: Merlin Moncure @ 2006-10-21 02:50 UTC (permalink / raw)
To: Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance; +Cc: alvherre@commandprompt.com
On 10/21/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Carlo Stonebanks wrote:
> > Our Windows-based db server has to integrate with users that work regularily
> > with Access.When attempting to import user's data from Access MDB files to
> > PostgreSQL, we try on eof two things: either import using EMS SQL Manager's
> > Data Import from Access utility, or export from Access to Postgresql via an
> > odbc-based connectionin both cases, the performance is just awful.
> > Performance with Tcl's native postgres driver seems rather fine running from
> > Windows a Windows client, BTW.
> >
> > ODBC is often blamed for this sort of thing - I have the 8.01.02 release
> > dated 2006.01.31. Everything appears to be at its default setting.
>
> Try Command Prompt's ODBC driver. Lately it has been measured to be
> consistently faster than psqlODBC.
>
> http://projects.commandprompt.com/public/odbcng
just curious: what was the reasoning to reimplement the protocol stack
in odbcng? the mainline odbc driver went in the other direction.
carlo: please, please, get your mail server to quit telling me your
mailbox is full :)
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 03:39 Joshua D. Drake <jd@commandprompt.com>
parent: Merlin Moncure <mmoncure@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Joshua D. Drake @ 2006-10-21 03:39 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance; alvherre@commandprompt.com
>> > ODBC is often blamed for this sort of thing - I have the 8.01.02
>> release
>> > dated 2006.01.31. Everything appears to be at its default setting.
>>
>> Try Command Prompt's ODBC driver. Lately it has been measured to be
>> consistently faster than psqlODBC.
I should note that we need to get a build out for Windows for rev 80.
Rev 80 is the one the build showing the most promise on Linux32 and 64
bit. It is also the one that reflects the performance metrics we have
been seeing.
We should have that in a week or so.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 03:40 Joshua D. Drake <jd@commandprompt.com>
parent: Merlin Moncure <mmoncure@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Joshua D. Drake @ 2006-10-21 03:40 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance; alvherre@commandprompt.com
>> Try Command Prompt's ODBC driver. Lately it has been measured to be
>> consistently faster than psqlODBC.
>>
>> http://projects.commandprompt.com/public/odbcng
>
> just curious: what was the reasoning to reimplement the protocol stack
> in odbcng? the mainline odbc driver went in the other direction.
We wanted to be able to offer some options that we couldn't if based
around libpq.
Sincerely,
Joshua D. Drake
>
> carlo: please, please, get your mail server to quit telling me your
> mailbox is full :)
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 15:25 Carlo Stonebanks <stonec.register@sympatico.ca>
parent: Merlin Moncure <mmoncure@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Carlo Stonebanks @ 2006-10-21 15:25 UTC (permalink / raw)
To: pgsql-performance
> carlo: please, please, get your mail server to quit telling me your
> mailbox is full :)
Merlin, sorry about that. This is the first I've heard of it.
Carlo
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 16:07 Alvaro Herrera <alvherre@commandprompt.com>
parent: Merlin Moncure <mmoncure@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2006-10-21 16:07 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance; jd@commandprompt.com
Merlin Moncure wrote:
> On 10/21/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >Try Command Prompt's ODBC driver. Lately it has been measured to be
> >consistently faster than psqlODBC.
> >
> >http://projects.commandprompt.com/public/odbcng
>
> just curious: what was the reasoning to reimplement the protocol stack
> in odbcng? the mainline odbc driver went in the other direction.
Yeah, but they had to back-off from that plan, and AFAIK it only uses
libpq for the auth stuff and then switch to dealing with the protocol
directly.
I don't know what the reasoning was though :-) I guess Joshua would
know. I'm not involved in that project. I only know that recently a
user posted some measurements showing that ODBCng was way slower that
psqlODBC, and it was discovered that it was using v3 Prepare/Bind/
Execute, which was problematic performance-wise due to the planner
issues with that. So AFAIK it currently parses the statements
internally before passing them to the server.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-21 16:14 Joshua D. Drake <jd@commandprompt.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 10+ messages in thread
From: Joshua D. Drake @ 2006-10-21 16:14 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Carlo Stonebanks <stonec.register@sympatico.ca>; pgsql-performance; jd@commandprompt.com
> Yeah, but they had to back-off from that plan, and AFAIK it only uses
> libpq for the auth stuff and then switch to dealing with the protocol
> directly.
>
> I don't know what the reasoning was though :-) I guess Joshua would
> know. I'm not involved in that project. I only know that recently a
> user posted some measurements showing that ODBCng was way slower that
> psqlODBC, and it was discovered that it was using v3 Prepare/Bind/
> Execute, which was problematic performance-wise due to the planner
> issues with that. So AFAIK it currently parses the statements
> internally before passing them to the server.
That is correct, we were using PostgreSQL server side prepare which has
shown to be ridiculously slow. So we moved to client side prepare and
ODBCng now moves very, very quickly.
You can see results here:
http://projects.commandprompt.com/public/odbcng/wiki/Performance
As in, it moves quickly enough to compete with other bindings such as
DBD::Pg.
One of the libpq features we wanted to avoid was the receiving of all
results on the server before sending to the client. With ODBCng we have
a buffering option that will receive all results over the wire directly.
This can increase performance quite a bit in specific circumstances but
also has the downside of using more memory on the ODBC client.
We also have a security through obscurity feature as described here:
http://projects.commandprompt.com/public/odbcng/wiki/PatternMatch
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Is ODBC that slow?
@ 2006-10-24 21:49 Carlo Stonebanks <stonec.register@sympatico.ca>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 10+ messages in thread
From: Carlo Stonebanks @ 2006-10-24 21:49 UTC (permalink / raw)
To: pgsql-performance
> Try Command Prompt's ODBC driver. Lately it has been measured to be
> consistently faster than psqlODBC.
>
> http://projects.commandprompt.com/public/odbcng
Thanks,
I tried this, but via Access it always reports a login (username/password)
to db failure. However, this a an Alpha - is there an "official" release I
should be waiting for? It's not clear to me whether this is a commercial
product or not.
Carlo
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-10-24 21:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-20 22:19 Is ODBC that slow? Carlo Stonebanks <stonec.register@sympatico.ca>
2006-10-21 01:04 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-21 02:32 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-10-21 02:50 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-21 03:39 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-21 03:40 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-21 15:25 ` Carlo Stonebanks <stonec.register@sympatico.ca>
2006-10-21 16:07 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-10-21 16:14 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-24 21:49 ` Carlo Stonebanks <stonec.register@sympatico.ca>
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