Обсуждение: Is ODBC that slow?
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
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
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.
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
>> 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
>> > 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
> 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
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
> 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
> 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