Dear pgsql-odbc support
I am currently working on a pilot for porting an application to
PostgreSQL. In the pilot I am examining the two interfaces ODBC and
ECPG, and evaluating their performance. I am aware that there is a
performance penalty for using ODBC, and I would like to pose a few
questions, hoping that I can further improve the performace using ODBC:
1. Studing the postgreSQL log, it is apparent that the the ODBC driver
switches off the Genetic Query Optimiser (geqo) and tries to switch on
another optimiser called (ksqo) which fails. What is the ksqo optimiser?
Why does it switch to a different optimiser? And why does the switch to
the ksqo optimiser fail?
2. Also from the postgreSQL log, it is apparent that with the ODBC
driver, client threads tend to get "serialised", while with ECPG threads
execute more concurrently (context switches occur more often). I have
created two test harnesses (see the attached .cc files) one using ODBC
via OTL4 and the other using ECPG. Specifically, ECPG tends to
"multitask" threads to the degree that some threads get to be active
twice, inserting some rows the first time and the rest the second time.
This is more noticable for a small number of threads with say 10 inserts
each; in such case thread activity with ODBC is strictly serialised,
while with ECPG multitasking is still evident. Does this serialisation
have any performance implications? (Is this maybe related to the
switched-off query optimiser mentioned above?).
3. Also, using ODBC, in the log file of PostgreSQL, it is seen that a
lot of blank statements are generated. Can these blank statements affect
the performance?
My system specifications are:
Solaris 8 (5.8) with the 113261-02 patch applied (as required by
unixODBC)
PostgreSQL 7.4
unixODBC 2.2.8 using the PostgreSQL driver that comes with it
(libodbcpsql.so.2.0.0, libodbcpsqlS.so.1.0.0)
OTL Version 4
I would appreciate your comments/help.
Regards,
Stergios Zisakis
NMS Department
Intracom
Athens
GREECE.
The attached file contains the following items:
insert_ecpg.pgc #the ECPG test harness
insert_odbc.cc #the ODBC test harness
insert_ecpg_1000rows.log #the PGSQL log using ECPG with 10 threads each
#inserting 100 rows.
insert_odbc_1000rows.log #the PGSQL log using ODBC with 10 threads each
#inserting 100 rows.
threads_ecpg.stats #log extracts demonstrating thread competition
#in terms of how many rows are inserted
threads_odbc.stats #each time a thread gets active.