Обсуждение: ByteA column retrieved in 31 minutes ...

Hi, <br /> I'm using a 9.0.2 db in a PowerBuilder 12.1 application, connecting to it via ODBC driver 09.00.0200.<br
/><br/> I have a table with a bytea column:<br /><br /><tt>CREATE TABLE docs<br /> (<br />   blobid character(7) NOT
NULL,<br/>   descript character varying(40),<br />   data date,<br />   "lock" character(1),<br />   ora
character(5),<br/>   parita character(1),<br />   tipo character(3),<br />   dbvtesto bytea,<br />   CONSTRAINT
docs_pkeyPRIMARY KEY (blobid),<br />   CONSTRAINT fk1_docs FOREIGN KEY (blobid)<br />       REFERENCES blob (blobid)
MATCHSIMPLE<br />       ON UPDATE NO ACTION ON DELETE CASCADE<br /> )<br /> WITH (<br />   OIDS=FALSE<br /> );</tt><br
/><br/><br /> When I retrieve via embedded SQL the dbvtesto column (using PK obv.) I get it in few seconds if its
lengthis about 10 KB, but I've to wait 31 minutes (!) if it's about 1 MB.<br /><br /> If someone has a proposal ... <br
/><br/> The <b>logs</b>:<br /><u>psqlodbclog</u>:<br /><br /> [70.282]conn=0F0EB070, query='select
dbvtesto                    FROM docs WHERE blobid ='000005T' '<br /> [84.635]    [ fetched 1 rows ]<br />
[84.657]conn=0F0EB070,query='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, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd'
thent.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
c.relnamespaceand c.oid = 252865) 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
andd.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'<br /> [84.694]    [ fetched 8
rows]<br /> [84.745]PGAPI_Columns: table='docs',field_name='blobid',type=1042,name='bpchar'<br />
[84.773]PGAPI_Columns:table='docs',field_name='descript',type=1043,name='varchar'<br /> [84.803]PGAPI_Columns:
table='docs',field_name='data',type=1082,name='date'<br/> [84.829]PGAPI_Columns:
table='docs',field_name='lock',type=1042,name='bpchar'<br/> [84.858]PGAPI_Columns:
table='docs',field_name='ora',type=1042,name='bpchar'<br/> [84.888]PGAPI_Columns:
table='docs',field_name='parita',type=1042,name='bpchar'<br/> [84.916]PGAPI_Columns:
table='docs',field_name='tipo',type=1042,name='bpchar'<br/> [84.945]PGAPI_Columns:
table='docs',field_name='dbvtesto',type=17,name='bytea'<br/> [1886.060]conn=0F0EB070, query='select
dbvcargo                    FROM blob WHERE blobid ='000005T' '<br /><br /><u>mylog</u>:<br />
[14596-70.280]CC_send_query:conn=0F0EB070, query='select dbvtesto                     FROM docs WHERE blobid ='000005T'
'<br/> [14596-70.283]send_query: done sending query 76bytes flushed<br /> [14596-70.284]in QR_Constructor<br />
[14596-70.285]exitQR_Constructor<br /> [14596-70.285]read -1, global_socket_buffersize=4096<br />
[14596-70.286]Lasterror=10035<br/> [14596-70.330]read 4096, global_socket_buffersize=4096<br />
[14596-70.331]send_query:got id = 'T'<br /> [14596-70.331]QR_fetch_tuples: cursor = '', self->cursor=00000000<br />
[14596-70.332]num_fields= 1<br /> [14596-70.333]READING ATTTYPMOD<br /> [14596-70.333]CI_read_fields:
fieldname='dbvtesto',adtid=17, adtsize=-1, atttypmod=-1 (rel,att)=(252865,8)<br /> [14596-70.335]QR_fetch_tuples: past
CI_read_fields:num_fields = 1<br /> [14596-70.336]MALLOC: tuple_size = 100, size = 800<br />
[14596-70.336]QR_next_tuple:inTuples = true, falling through: fcount = 0, fetch_number = 0<br /> [14596-70.338]read
4096,global_socket_buffersize=4096<br /><br /> 766 similar rows + last at 2315 <br /><br /> [14596-70.594]read 2315,
global_socket_buffersize=4096<br/> [14596-70.594]qresult: len=3139786, buffer=' ....<br /><br /> the text ...<br /><br
/>[14596-84.635]end of tuple list -- setting inUse to false: this = 0F0EE560 SELECT 1<br />
[14596-84.636]_QR_next_tuple:'C' fetch_total = 1 & this_fetch = 1<br /> [14596-84.636]QR_next_tuple: backend_rows
<CACHE_SIZE: brows = 0, cache_size = 0<br /> [14596-84.636]QR_next_tuple: reached eof now<br />
[14596-84.637]send_query:got id = 'Z'<br /> [14596-84.637]     done sending the query:<br />
[14596-84.637]extend_column_bindings:entering ... self=0F0EE7C0, bindings_allocated=0, num_columns=1<br />
[14596-84.637]exitextend_column_bindings=1110E070<br /><br /> many similar rows, the blocks are repeated on 40000 bytes
step<br/> n.b.: 40000 is the value of MaxVarchar in ODBC configuration and ByteaAsLo = 0 (but set it to 1 doesn't
changethe behaviour)<br /><br /> [14596-801.327]SQL_C_BINARY: len = 929892, copy_len = 40000<br />
[14596-801.327]STATEMENTWARNING: func=PGAPI_GetData, desc='', errnum=-2, errmsg='The buffer was too small for the
GetData.'<br/> [14596-801.328][SQLGetData][14596-801.328]PGAPI_GetData: enter, stmt=0F0EE738 icol=1<br />
[14596-801.329]    num_rows = 1<br /> [14596-801.329]     value = '<br /><br /><br /> [14596-815.310]****
PGAPI_GetData:icol = 0, target_type = -2, field_type = 17, value = '<br /><br /> [14596-829.403]copy_and_convert:
field_type= 17, fctype = -2, value = '....<br /><br /> [14596-843.453]SQL_C_BINARY: len = 889892, copy_len = 40000<br
/>[14596-843.453]STATEMENT WARNING: func=PGAPI_GetData, desc='', errnum=-2, errmsg='The buffer was too small for the
GetData.'<br/> [14596-843.454][SQLGetData][14596-843.454]PGAPI_GetData: enter, stmt=0F0EE738 icol=1<br />
[14596-843.454]    num_rows = 1<br /> [14596-843.454]     value = '...<br /><br /><br /><b>The ODBC
configuration</b>:<br/> "Driver"="C:\\Programmi\\PostgreSQL\\9.0\\PSQLODBC35W.DLL"<br /> "CommLog"="1"<br />
"Debug"="0"<br/> "Fetch"="100"<br /> "Optimizer"="0"<br /> "Ksqo"="0"<br /> "UniqueIndex"="1"<br />
"UseDeclareFetch"="0"<br/> "UnknownSizes"="0"<br /> "TextAsLongVarchar"="1"<br /> "UnknownsAsLongVarchar"="0"<br />
"BoolsAsChar"="1"<br/> "Parse"="0"<br /> "CancelAsFreeStmt"="0"<br /> "MaxVarcharSize"="40000"<br />
"MaxLongVarcharSize"="80000"<br/> "ExtraSysTablePrefixes"="dd_;"<br /> "Description"=""<br /> "Database"="Argos_UTF"<br
/>"Servername"="localhost"<br /> "Port"="5432"<br /> "Username"="postgres"<br /> "UID"="postgres"<br />
"Password"="postgres"<br/> "ReadOnly"="0"<br /> "ShowOidColumn"="0"<br /> "FakeOidIndex"="0"<br />
"RowVersioning"="0"<br/> "ShowSystemTables"="0"<br /> "Protocol"="7.4-1"<br /> "ConnSettings"=""<br />
"DisallowPremature"="0"<br/> "UpdatableCursors"="1"<br /> "LFConversion"="1"<br /> "TrueIsMinus1"="0"<br /> "BI"="0"<br
/>"AB"="0"<br /> "ByteaAsLongVarBinary"="0"<br /> "UseServerSidePrepare"="0"<br /> "LowerCaseIdentifier"="0"<br />
"GssAuthUseGSS"="0"<br/> "SSLmode"="disable"<br /> "XaOpt"="1"<br /><br /><br /><div class="moz-signature">-- <br
/></div><p><div><br/></div> 

Hi Andrea,

(2012/07/17 23:46), Andrea Ricci wrote:
> Hi,
> I'm using a 9.0.2 db in a PowerBuilder 12.1 application, connecting to
> it via ODBC driver 09.00.0200.
>
> I have a table with a bytea column:
>
> CREATE TABLE docs
> (
>    blobid character(7) NOT NULL,
>    descript character varying(40),
>    data date,
>    "lock" character(1),
>    ora character(5),
>    parita character(1),
>    tipo character(3),
>    dbvtesto bytea,
>    CONSTRAINT docs_pkey PRIMARY KEY (blobid),
>    CONSTRAINT fk1_docs FOREIGN KEY (blobid)
>        REFERENCES blob (blobid) MATCH SIMPLE
>        ON UPDATE NO ACTION ON DELETE CASCADE
> )
> WITH (
>    OIDS=FALSE
> );
>
>
> When I retrieve via embedded SQL the dbvtesto column (using PK obv.) I
> get it in few seconds if its length is about 10 KB, but I've to wait 31
> minutes (!) if it's about 1 MB.
>
> If someone has a proposal ...

Mylog is a performance killer for this kind of query.
How about disabling Mylog?

regards,
Hiroshi Inoue

<div class="moz-cite-prefix">Thanks ! <br /><br /> I have both the logs up, for testing purpose, and removing mylog
forcethe time of retrieval to be normal (1 sec)<br /><br /> Andrea<br /><br /><br /> Il 07/17/2012 23.11, Hiroshi Inoue
hascritto:<br /></div><blockquote cite="mid:5005D517.3020809@tpf.co.jp" type="cite">Hi Andrea, <br /><br /> (2012/07/17
23:46),Andrea Ricci wrote: <br /><blockquote type="cite">Hi, <br /> I'm using a 9.0.2 db in a PowerBuilder 12.1
application,connecting to <br /> it via ODBC driver 09.00.0200. <br /><br /> I have a table with a bytea column: <br
/><br/> CREATE TABLE docs <br /> ( <br />    blobid character(7) NOT NULL, <br />    descript character varying(40),
<br/>    data date, <br />    "lock" character(1), <br />    ora character(5), <br />    parita character(1), <br />   
tipocharacter(3), <br />    dbvtesto bytea, <br />    CONSTRAINT docs_pkey PRIMARY KEY (blobid), <br />    CONSTRAINT
fk1_docsFOREIGN KEY (blobid) <br />        REFERENCES blob (blobid) MATCH SIMPLE <br />        ON UPDATE NO ACTION ON
DELETECASCADE <br /> ) <br /> WITH ( <br />    OIDS=FALSE <br /> ); <br /><br /><br /> When I retrieve via embedded SQL
thedbvtesto column (using PK obv.) I <br /> get it in few seconds if its length is about 10 KB, but I've to wait 31 <br
/>minutes (!) if it's about 1 MB. <br /><br /> If someone has a proposal ... <br /></blockquote><br /> Mylog is a
performancekiller for this kind of query. <br /> How about disabling Mylog? <br /><br /> regards, <br /> Hiroshi Inoue
<br/><br /></blockquote><br /><br /><div class="moz-signature">-- <br /></div><p><div><p class="Stile5">Andrea Ricci<p
class="Stile3">ProductManager<p><a href="Logo_mail.jpg"><img height="45" src="cid:part1.04090304.04090709@dedalus.eu"
width="118"/></a><p class="Stile2">Via di Collodi, 6/C  50141 Firenze (Italy) <p class="Stile4"><span
class="Stile4"> <spanlang="fr" xml:lang="fr">tel. +39 055 4247.4661</span></span><p class="Stile4"><span class="Stile1"
lang="EN-GB"xml:lang="EN-GB"> </span><span class="Stile1" lang="fr" xml:lang="fr">mail </span><span
class="Stile1"><u><spanclass="Stile6" lang="EN-GB" xml:lang="EN-GB"><a class="moz-txt-link-abbreviated"
href="mailto:andrea.ricci@dedalus.eu">andrea.ricci@dedalus.eu</a></span></u></span><pclass="Stile1"><span
class="Stile4">___________________________________________________________________________________________________________<br
/>Le informazioni contenute nella presente comunicazione ed i relativi allegati possono essere riservate e sono,
comunque,destinate esclusivamente alle persone o alle Società in indirizzo. La diffusione, comunicazione, distribuzione
e/ocopiatura delle informazioni/documenti trasmesse da parte di qualsiasi soggetto diverso dal destinatario o non
autorizzatoè proibita, sia ai sensi dell'art. 616 C.P., sia ai sensi del D. lgs. 196/2003. Se avete ricevuto questo
messaggioper errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 055.42471 o inviando
unmessaggio all'indirizzo e- mail ( <a class="Stile6"
href="mailto:andrea.ricci@dedalus.eu">andrea.ricci@dedalus.eu</a> ).Si fa, inoltre, presente che l'indirizzo di posta
elettronicada cui è stata inviata la presente comunicazione è Aziendale e non personale, qualsiasi comunicazione ivi
inviatapuò essere quindi letta anche da incaricati aziendali diversi dal suo utilizzatore
principale<em>.</em></span></div>