Re: Extremely Low performance with ODBC

From: "Sebastian Rychter" <srychter(at)anvet(dot)com(dot)ar>
To: "'Richard Broersma'" <richard(dot)broersma(at)gmail(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Extremely Low performance with ODBC
Date: 2008-05-27 03:26:36
Message-ID: 023201c8bfa9$7c875000$7595f000$@com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Richard,

I'm not sure if this is the kind of log that you meant, but the one in
pg_log goes like this (the first query corresponding to the ODBC execution
(112157 ms), the second one is from pgAdmin (250 ms). I also logged for the
ODBC driver but didn't find any differences between the steps followed in
both cases.

2008-05-27 02:55:10 GMT LOG: duration: 112157.000 ms statement: SELECT
"Patient"."Patient_ID"
FROM "Patient",
"PatientFamily",
"PatientFamilyRole",
"Title",
"City" PatientCity,
"City" PatientAltCity,
"City" PatientEmployerCity,
"City" PatientDoctorCity,
"Province" PatientProvince,
"Province" PatientAltProvince,
"Province" PatientEmployerProvince,
"Province" PatientDoctorProvince,
"Country" PatientCountry,
"Country" PatientAltCountry,
"Country" PatientEmployerCountry,
"Country" PatientDoctorCountry
WHERE "Patient"."Family_ID"="PatientFamily"."Family_ID" AND

"Patient"."FamilyRole_ID"="PatientFamilyRole"."FamilyRole_ID" AND
"Patient"."Title_ID"="Title"."Title_ID" AND
"Patient"."City_ID"=PatientCity."City_ID" AND
"Patient"."Province_ID"=PatientProvince."Province_ID" AND
"Patient"."Country_ID"=PatientCountry."Country_ID" AND
"Patient"."AltCity_ID"=PatientAltCity."City_ID" AND
"Patient"."AltProvince_ID"=PatientAltProvince."Province_ID"
AND
"Patient"."AltCountry_ID"=PatientAltCountry."Country_ID" AND
"Patient"."EmployerCity_ID"=PatientEmployerCity."City_ID" AND

"Patient"."EmployerProvince_ID"=PatientEmployerProvince."Province_ID" AND

"Patient"."EmployerCountry_ID"=PatientEmployerCountry."Country_ID" AND
"Patient"."DoctorCity_ID"=PatientDoctorCity."City_ID" AND

"Patient"."DoctorProvince_ID"=PatientDoctorProvince."Province_ID" AND
"Patient"."DoctorCountry_ID"=PatientDoctorCountry."Country_ID"
AND
"Patient"."Patient_ID"=10450

2008-05-27 03:00:07 GMT LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-05-27 03:00:08 GMT LOG: duration: 108.999 ms statement: set
client_encoding to 'UNICODE'
2008-05-27 03:00:11 GMT LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-05-27 03:00:12 GMT LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2008-05-27 03:00:14 GMT ERROR: unterminated quoted string at or near "' "
at character 554
2008-05-27 03:00:14 GMT STATEMENT: select n.nspname, c.relname, a.attname,
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner
join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid =
3368883) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and
a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on
t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and
d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname,
attnum'
2008-05-27 03:07:29 GMT LOG: duration: 250.000 ms statement: SELECT
"Patient"."Patient_ID"
FROM "Patient",
"PatientFamily",
"PatientFamilyRole",
"Title",
"City" PatientCity,
"City" PatientAltCity,
"City" PatientEmployerCity,
"City" PatientDoctorCity,
"Province" PatientProvince,
"Province" PatientAltProvince,
"Province" PatientEmployerProvince,
"Province" PatientDoctorProvince,
"Country" PatientCountry,
"Country" PatientAltCountry,
"Country" PatientEmployerCountry,
"Country" PatientDoctorCountry
WHERE "Patient"."Family_ID"="PatientFamily"."Family_ID" AND

"Patient"."FamilyRole_ID"="PatientFamilyRole"."FamilyRole_ID" AND
"Patient"."Title_ID"="Title"."Title_ID" AND
"Patient"."City_ID"=PatientCity."City_ID" AND

"Patient"."Province_ID"=PatientProvince."Province_ID" AND
"Patient"."Country_ID"=PatientCountry."Country_ID"
AND
"Patient"."AltCity_ID"=PatientAltCity."City_ID" AND

"Patient"."AltProvince_ID"=PatientAltProvince."Province_ID" AND

"Patient"."AltCountry_ID"=PatientAltCountry."Country_ID" AND

"Patient"."EmployerCity_ID"=PatientEmployerCity."City_ID" AND

"Patient"."EmployerProvince_ID"=PatientEmployerProvince."Province_ID" AND

"Patient"."EmployerCountry_ID"=PatientEmployerCountry."Country_ID" AND
"Patient"."DoctorCity_ID"=PatientDoctorCity."City_ID"
AND

"Patient"."DoctorProvince_ID"=PatientDoctorProvince."Province_ID" AND

"Patient"."DoctorCountry_ID"=PatientDoctorCountry."Country_ID" AND
"Patient"."Patient_ID"=10450

-----Mensaje original-----
De: Richard Broersma [mailto:richard(dot)broersma(at)gmail(dot)com]
Enviado el: Viernes, 23 de Mayo de 2008 07:00 p.m.
Para: Sebastian Rychter
CC: pgsql-sql(at)postgresql(dot)org
Asunto: Re: [SQL] Extremely Low performance with ODBC

On Fri, May 23, 2008 at 2:45 PM, Sebastian Rychter
<srychter(at)anvet(dot)com(dot)ar> wrote:

> Thanks for your response. I did that in the first place and found that
both
> explain reports where quite similar. I've got both reports: From pgadmin
it
> took 7288 ms, and from VFP took 6446 ms (of course, that's the time
> calculated within the explain analyze, and not the real elapsed time).

actually it looks like 7.288ms and 6.446ms. ;)

> That's why I assume the ODBC driver is taking its own time for parsing or
> something like that- around 2 minutes actually :)

Checking the Postgresql logs would be really helpful here.

1) note the time that you issue the query from your client FE.
2) note the time in the pg server logs that the server gets the passed
query string.
2b) check for any disparity between 1) and 2).
3) check that the query is exactly the same as you've hand coded in pg-admin
4) also check the query duration when issued by ODBC in the postgresql
log to see if it is really taking 2 minutes.

It would be nice to see the snippet of the PG log showing this ODBC
query with associate duration times.

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

__________ NOD32 3127 (20080523) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma 2008-05-27 04:13:53 Re: Extremely Low performance with ODBC
Previous Message Nacef LABIDI 2008-05-25 21:51:06 I invite you to join my Ziki Network !