Re: libpq vs ODBC

Is there any performance penalty when I use ODBC library vs using libpq?

Best Regards,
Divakar

,--- You/Divakar (Wed, 8 Dec 2010 20:31:30 -0800 (PST)) ----*
| Is there any performance penalty when I use ODBC library vs using libpq?

In general, yes.

In degenerate cases when most of the work happens in the server, no.

You need to measure in the contents of your specific application.

-- Alex -- alex-goncharov(at)comcast(dot)net --

So it means there will be visible impact if the nature of DB interaction is DB
insert/select. We do that mostly in my app.
Performance difference would be negligible if the query is server intensive
where execution time is far more than time taken by e.g. communication interface
or transaction handling.
Am I right?

Best Regards,
Divakar

________________________________
From: Alex Goncharov <alex-goncharov(at)comcast(dot)net>
To: Divakar Singh <dpsmails(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Sent: Thu, December 9, 2010 10:31:17 AM
Subject: Re: [PERFORM] libpq vs ODBC

,--- You/Divakar (Wed, 8 Dec 2010 20:31:30 -0800 (PST)) ----*
| Is there any performance penalty when I use ODBC library vs using libpq?

In general, yes.

In degenerate cases when most of the work happens in the server, no.

You need to measure in the contents of your specific application.

-- Alex -- alex-goncharov(at)comcast(dot)net --

,--- You/Divakar (Wed, 8 Dec 2010 21:17:22 -0800 (PST)) ----*
| So it means there will be visible impact if the nature of DB interaction is DB
| insert/select. We do that mostly in my app.

You can't say a "visible impact" unless you can measure it in your
specific application.

Let's say ODBC takes 10 times of .001 sec for libpq. Is this a
"visible impact"?

| Performance difference would be negligible if the query is server intensive
| where execution time is far more than time taken by e.g. communication interface
| or transaction handling.
| Am I right?

You've got to measure -- there are too many variables to give you the
answer you are trying to get.

To a different question, "Would I use ODBC to work with PostgreSQL if
I had the option of using libpq?", I'd certainly answer, "No".

You'd need to have the option of using libpq, though. ODBC takes care
of a lot of difficult details for you, and libpq's higher performance
may turn out to be a loss for you, in your specific situation.

-- Alex -- alex-goncharov(at)comcast(dot)net --

hmm
If I understand it correctly you argument is valid from performance point of
view.
But in practical scenarios, it would make more sense to do ODBC if the
difference is only 5% or so, because it opens up so many choices of databases
for me.
Do we have some published data in this area.

Best Regards,
Divakar

________________________________
From: Alex Goncharov <alex-goncharov(at)comcast(dot)net>
To: Divakar Singh <dpsmails(at)yahoo(dot)com>
Cc: alex-goncharov(at)comcast(dot)net; pgsql-performance(at)postgresql(dot)org
Sent: Thu, December 9, 2010 11:21:26 AM
Subject: Re: [PERFORM] libpq vs ODBC

,--- You/Divakar (Wed, 8 Dec 2010 21:17:22 -0800 (PST)) ----*
| So it means there will be visible impact if the nature of DB interaction is DB

| insert/select. We do that mostly in my app.

You can't say a "visible impact" unless you can measure it in your
specific application.

Let's say ODBC takes 10 times of .001 sec for libpq. Is this a
"visible impact"?

| Performance difference would be negligible if the query is server intensive
| where execution time is far more than time taken by e.g. communication
interface

| or transaction handling.
| Am I right?

You've got to measure -- there are too many variables to give you the
answer you are trying to get.

To a different question, "Would I use ODBC to work with PostgreSQL if
I had the option of using libpq?", I'd certainly answer, "No".

You'd need to have the option of using libpq, though. ODBC takes care
of a lot of difficult details for you, and libpq's higher performance
may turn out to be a loss for you, in your specific situation.

-- Alex -- alex-goncharov(at)comcast(dot)net --

Hello

2010/12/9 Divakar Singh <dpsmails(at)yahoo(dot)com>:
> hmm
> If I understand it correctly you argument is valid from performance point of
> view.
> But in practical scenarios, it would make more sense to do ODBC if the
> difference is only 5% or so, because it opens up so many choices of
> databases for me.
> Do we have some published data in this area.
>

It's depend on your environment - VB or VBA has not native drivers, so
you have to use a ODBC. The overhead from ODBC or ADO or ADO.NET for
almost task unsignificant. So people use it. The performance problems
can be detected in some special tasks - and then is necessary to use a
stored procedures.

Regards

Pavel Stehule

>
> Best Regards,
> Divakar
>
> ________________________________
> From: Alex Goncharov <alex-goncharov(at)comcast(dot)net>
> To: Divakar Singh <dpsmails(at)yahoo(dot)com>
> Cc: alex-goncharov(at)comcast(dot)net; pgsql-performance(at)postgresql(dot)org
> Sent: Thu, December 9, 2010 11:21:26 AM
> Subject: Re: [PERFORM] libpq vs ODBC
>
> ,--- You/Divakar (Wed, 8 Dec 2010 21:17:22 -0800 (PST)) ----*
> | So it means there will be visible impact if the nature of DB interaction
> is DB
> | insert/select. We do that mostly in my app.
>
> You can't say a "visible impact" unless you can measure it in your
> specific application.
>
> Let's say ODBC takes 10 times of .001 sec for libpq.  Is this a
> "visible impact"?
>
> | Performance difference would be negligible if the query is server
> intensive
> | where execution time is far more than time taken by e.g. communication
> interface
> | or transaction handling.
> | Am I right?
>
> You've got to measure -- there are too many variables to give you the
> answer you are trying to get.
>
> To a different question, "Would I use ODBC to work with PostgreSQL if
> I had the option of using libpq?", I'd certainly answer, "No".
>
> You'd need to have the option of using libpq, though.  ODBC takes care
> of a lot of difficult details for you, and libpq's higher performance
> may turn out to be a loss for you, in your specific situation.
>
> -- Alex -- alex-goncharov(at)comcast(dot)net --
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>

On Thu, 09 Dec 2010 06:51:26 +0100, Alex Goncharov
<alex-goncharov(at)comcast(dot)net> wrote:

> ,--- You/Divakar (Wed, 8 Dec 2010 21:17:22 -0800 (PST)) ----*
> | So it means there will be visible impact if the nature of DB
> interaction is DB
> | insert/select. We do that mostly in my app.
>
> You can't say a "visible impact" unless you can measure it in your
> specific application.
>
> Let's say ODBC takes 10 times of .001 sec for libpq. Is this a
> "visible impact"?

Well you have to consider server and client resources separately. If you
waste a bit of CPU time on the client by using a suboptimal driver, that
may be a problem, or not. It you waste server resources, that is much more
likely to be a problem, because it is multiplied by the number of clients.
I don't know about the specifics of ODBC performance, but for instance
php's PDO driver's handling of prepared statements with postgres comes up
as an example of what not to do.