Re: [SQL] Extremely Low performance with ODBC

2008-05-26 Thread Sebastian Rychter 
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"=Patien

Re: [SQL] Extremely Low performance with ODBC

2008-05-26 Thread Richard Broersma 
On Mon, May 26, 2008 at 8:26 PM, Sebastian Rychter
<[EMAIL PROTECTED]> wrote:

> 2008-05-27 02:55:10 GMT LOG:  duration: 112157.000 ms  statement: SELECT
> 2008-05-27 03:07:29 GMT LOG:  duration: 250.000 ms  statement: SELECT

Wow, that is a big difference.  I reformated your querys and diff'ed
them.  You are correct, they are exactly the same query.  My only
other guess would be that the first time you execute this query using
ODBC, you need to get a fresh read from disk.  However, once this
dataset is cached in memory it returns much faster when you requery it
using PGAdmin.  If you run the same query twice conseqitively in ODBC,
does the second query perform much better or do both querys perform
badly?

If they both odbc queries perform badly, the last thing that I would
try would be to capture the results of :

EXPLAIN ANALYZE SELECT ...(your query), from both ODBC, and PGADMIN.
If we compare them It should at least tell us what the server is doing
differently that is causing the query mis-behavior.


-- 
Regards,
Richard Broersma Jr.

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Extremely Low performance with ODBC

2008-05-26 Thread Sebastian Rychter 
I already tried executing them in different order, but that only affects in
just a few milliseconds (the one with explicit joins remains much faster).
The bad news is that I already compared both Explain Analyze results (from
pgAdmin and ODBC) and they follow the same steps. I also tried comparing the
Explain Analyze between the Explicit Join and the Implicit Join, and though
they are different, the Total time is almost the same.

I'll keep on looking for any other differences on different logs.. I think I
already searched all the Postgresql forums I know and no one ever reported
something like that before.

Thanks once again,

Sebastian

-Mensaje original-
De: Richard Broersma [mailto:[EMAIL PROTECTED] 
Enviado el: Martes, 27 de Mayo de 2008 01:14 a.m.
Para: Sebastian Rychter
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Extremely Low performance with ODBC

On Mon, May 26, 2008 at 8:26 PM, Sebastian Rychter
<[EMAIL PROTECTED]> wrote:

> 2008-05-27 02:55:10 GMT LOG:  duration: 112157.000 ms  statement: SELECT
> 2008-05-27 03:07:29 GMT LOG:  duration: 250.000 ms  statement: SELECT

Wow, that is a big difference.  I reformated your querys and diff'ed
them.  You are correct, they are exactly the same query.  My only
other guess would be that the first time you execute this query using
ODBC, you need to get a fresh read from disk.  However, once this
dataset is cached in memory it returns much faster when you requery it
using PGAdmin.  If you run the same query twice conseqitively in ODBC,
does the second query perform much better or do both querys perform
badly?

If they both odbc queries perform badly, the last thing that I would
try would be to capture the results of :

EXPLAIN ANALYZE SELECT ...(your query), from both ODBC, and PGADMIN.
If we compare them It should at least tell us what the server is doing
differently that is causing the query mis-behavior.


-- 
Regards,
Richard Broersma Jr.

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

__ NOD32 3133 (20080526) Information __

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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql