Re: Show max_identifier_length causes heavy query execution

Hi,

I'm the new connectivity engineer.

I'm attempting to recreate the problem, but haven't seen the same results as you.
Can you send me the sequence of ODBC statements that cause the problem?

Thanks,

....Ken Sell

On 5/26/09 9:48 AM, "Moreno D." <moreno(dot)d(at)hotmail(dot)it> wrote:

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! <http://messenger.it/gioca.aspx>