Skip site navigation (1) Skip section navigation (2)

Re: Show max_identifier_length causes heavy query execution

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Moreno D(dot) *EXTERN*" <moreno(dot)d(at)hotmail(dot)it>,<pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Show max_identifier_length causes heavy query execution
Date: 2009-05-27 07:16:58
Message-ID: D960CB61B694CF459DCFB4B0128514C202FF6600@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-odbc
Moreno D. wrote:
> I noticed that after executing a simple query that takes few 
> milliseconds, the odbc driver executes another query which 
> takes about 30 seconds to execute, because it scans a large table.
>
[...]
> 
> Then the driver executes the heavy query "SELECT * FROM 
> az001.doc101", which retrieves about 1.5 million rows.
> 
[...]
> 
> and this is the entire log file:
> 
[...]
> [0.613]conn=02E72C78, 
> PGAPI_DriverConnect(out)='DSN=PostgreSQL 
> Log;DATABASE=muna;SERVER=localhost;PORT=5432;UID=client;PWD=xx
> xxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;
> ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSetting
> s=;Fetch=100;Socket=4096;UnknownSizes=2;MaxVarcharSize=255;Max
> LongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseD
> eclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;Bool
> sAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_
> ;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIs
> Minus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;Lo
> werCaseIdentifier=0;XaOpt=1'

Without the ODBC statements it is difficult to say something conclusive,
but it *might* be that you have the same problem I had some time ago.

I see that you set "Parse=0" in your connection options.
This will result in the ODBC driver executing "SELECT *"-Statements
when it needs to get a column description.

If that is the cause of your problem, then setting "Parse=1"
will fix it.

In ODBC Administrator on Windows, this option is called "Parse Statements"
and can be found in the advanced options page 1 (Button "Datasource").

Yours,
Laurenz Albe

In response to

Responses

pgsql-odbc by date

Next:From: FarooqDate: 2009-05-28 05:25:56
Subject: ODBC driver and refcursors
Previous:From: Ken SellDate: 2009-05-26 20:25:53
Subject: Re: Show max_identifier_length causes heavy query execution

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group