Show max_identifier_length causes heavy query execution

Hello all,

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.
This is the part of the log file I think is showing the problem, see below for the entire log file:

[15.534]conn=02E72C78, query='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.552]    [ fetched 1 rows ]
[15.608]conn=02E72C78, query='show max_identifier_length'
[15.619]    [ fetched 1 rows ]
[15.660]STATEMENT ERROR: func=PGAPI_ColAttributes, desc='', errnum=30, errmsg='this request may be for MS SQL Server'

Then the driver executes the heavy query "SELECT * FROM az001.doc101", which retrieves about 1.5 million rows.

I came up with a workaround to avoid this problem, executing the query
SELECT (select count(*) from az001.doc101 where (code,riga)=(1,1))
instead of the original one, but this problem seems to appear in many points of the application, so i would like to find a better solution.

Here are some additional information,
the table structure is

CREATE TABLE az001.doc101
(
  code integer NOT NULL,
  riga integer NOT NULL,
  subriga integer NOT NULL,
  t_prog character varying(6) NOT NULL,
  fd_a character varying,
  fd_nd double precision,
  fd_il integer,
  fd_v double precision,
  fd_d timestamp with time zone,
  fd_sn boolean NOT NULL DEFAULT false,
  CONSTRAINT doc101_pkey PRIMARY KEY (code, riga, subriga, t_prog),
  CONSTRAINT doc101_code_fkey1 FOREIGN KEY (code, riga, subriga)
      REFERENCES az001.doc110 (code, riga, subriga) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);

and this is the entire log file:

[0.060]conn=02E72C78, PGAPI_DriverConnect( in)='DSN=PostgreSQL Log;', fDriverCompletion=0
[0.082]DSN info: DSN='PostgreSQL Log',server='localhost',port='5432',dbase='muna',user='client',passwd='xxxxx'
[0.091]          onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[0.097]          conn_settings='', conn_encoding='(null)'
[0.100]          translation_dll='',translation_option=''
[0.116]Driver Version='08.03.0300,200809260001' linking 1400 static Multithread library
[0.123]Global Options: fetch=100, socket=4096, unknown_sizes=2, max_varchar_size=255, max_longvarchar_size=8190
[0.131]                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
[0.133]                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[0.136]                extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
[0.544]    [ PostgreSQL version string = '8.3.4' ]
[0.545]    [ PostgreSQL version number = '8.3' ]
[0.565]conn=02E72C78, query='select oid, typbasetype from pg_type where typname = 'lo''
[0.586]    [ fetched 0 rows ]
[0.601]    [ Large Object oid = -999 ]
[0.602]    [ Client encoding = 'UTF8' (code = 6) ]
[0.613]conn=02E72C78, PGAPI_DriverConnect(out)='DSN=PostgreSQL Log;DATABASE=muna;SERVER=localhost;PORT=5432;UID=client;PWD=xxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=2;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
[15.534]conn=02E72C78, query='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.552]    [ fetched 1 rows ]
[15.608]conn=02E72C78, query='show max_identifier_length'
[15.619]    [ fetched 1 rows ]
[15.660]STATEMENT ERROR: func=PGAPI_ColAttributes, desc='', errnum=30, errmsg='this request may be for MS SQL Server'
[15.663]                 ------------------------------------------------------------
[15.664]                 hdbc=02E72C78, stmt=02E77808, result=02E70EA0
[15.665]                 prepare=0, internal=0
[15.666]                 bindings=02E791E8, bindings_allocated=1
[15.667]                 parameters=00000000, parameters_allocated=0
[15.668]                 statement_type=0, statement='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.670]                 stmt_with_params='select count(*) from az001.doc101 where (code,riga)=(1,1)
'
[15.671]                 data_at_exec=-1, current_exec_param=-1, put_data=0
[15.672]                 currTuple=-1, current_col=-1, lobj_fd=-1
[15.673]                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
[15.674]                 cursor_name='SQL_CUR02E77808'
[15.675]                 ----------------QResult Info -------------------------------
[15.677]                 fields=02E77BA8, backend_tuples=02E78E70, tupleField=48729712, conn=02E72C78
[15.678]                 fetch_count=0, num_total_rows=1, num_fields=1, cursor='(NULL)'
[15.679]                 message='(NULL)', command='SELECT', notice='(NULL)'
[15.680]                 status=100, inTuples=0
[15.681]CONN ERROR: func=PGAPI_ColAttributes, desc='', errnum=0, errmsg='(NULL)'
[15.683]            ------------------------------------------------------------
[15.685]            henv=02E72C38, conn=02E72C78, status=1, num_stmts=16
[15.687]            sock=02E70A08, stmts=02E70AE0, lobj_type=-999
[15.688]            ---------------- Socket Info -------------------------------
[15.690]            socket=552, reverse=0, errornumber=0, errormsg='(NULL)'
[15.692]            buffer_in=48715736, buffer_out=48719856
[15.692]            buffer_filled_in=76, buffer_filled_out=0, buffer_read_in=76
[15.735]CONN ERROR: func=SQLNativeSqlW, desc='', errnum=-2, errmsg='Sql string too large'
[15.737]            ------------------------------------------------------------
[15.738]            henv=02E72C38, conn=02E72C78, status=1, num_stmts=16
[15.739]            sock=02E70A08, stmts=02E70AE0, lobj_type=-999
[15.740]            ---------------- Socket Info -------------------------------
[15.741]            socket=552, reverse=0, errornumber=0, errormsg='(NULL)'
[15.743]            buffer_in=48715736, buffer_out=48719856
[15.743]            buffer_filled_in=76, buffer_filled_out=0, buffer_read_in=76
[15.772]conn=02E72C78, query='SELECT * FROM az001.doc101'


Thanks in advance, I apologize for my poor english.

Regards,

Moreno Dissegna
Developer
S.A. Software S.r.l.

---

Scrivi, parla e gioca con i tuoi amici! Scarica Messenger 2009!