Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fastest way to read tuples

Fastest way to read tuples

From
"Joachim Zauner"
Date:
Hi List,
I want to retrieve (readonly) 500 - 1500 Tuples via ODBC in an Visual Basic
6 Program over a 64kBit Line. The Raw Data of 500 Tuples is ~ 52kb but it
takes 7 Seconds to fill the Recordset.
Currently I use ADO to access the DB. I played around with Cursors but no
effect (with Serverside Cursor the execution of the SQL String is faster but
walking throug the Rows is slower).

The Problem is not the SQL String - the Server takes only 0.2 Seconds for
the Query but the Transfer of the Dataset is slow. Is RDO or DAO (or
whatever) faster?

Now some Info about my System:
ODBC Version: 07.02.0005
VB 6.0 Enterprise SP5
W2k Pro SP3
PostgreSQL 7.2.1 on SuSE Linux (for Development / 7.3 for Production)

My Connectionstring:
DRIVER={PostgreSQL+ (Beta)};DATABASE=ticket;SERVER=myServer
;PORT=5432;UID=myuser;PWD=mypass;ReadOnly=1;Protocol=6.4;FakeOidIndex=1;Show
OidColumn=1;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socke
t=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;Com
mLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLo
ngVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=d
d_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=
0'

Regards,
    j.zauner

Re: Fastest way to read tuples

From
Chris Gamache
Date:
500 tuples is 500 tuples whether you pull them all at once, or one at a time.
You can either take your time up-front or spread it out. Spreading it out would
have the smallest "psychological" impact on your users, yes?

I'm not sure you're going to get much faster on the client interface. Assuming
that you're PostgreSQL configuration is properly optimized and vacuumed, your
64k line would seem to be the bottleneck...

Having said that, a good trick for faster tuple access in ADO is the rs.getrows
method. If you're using rs.movenext, your eating up more client-side processor
power than you need. See www.devguru.com for more nifty ADO tricks.

set conn = createobject("adodb.connection")
conn.open connstr
mysql = "select * from test;"
set rs = conn.execute(mysql)
aRows = rs.getrows
sRow = ""
for j = 0 to ubound(aRows,2)
  for i = 0 to ubound(aRows,1)
    sRow = sRow & aRows(i,j)
  next
  debug.print(sRow)
  sRow = ""
next

HTH,

CG


--- Joachim Zauner <j.zauner@epcom.cc> wrote:
> Hi List,
> I want to retrieve (readonly) 500 - 1500 Tuples via ODBC in an Visual Basic
> 6 Program over a 64kBit Line. The Raw Data of 500 Tuples is ~ 52kb but it
> takes 7 Seconds to fill the Recordset.
> Currently I use ADO to access the DB. I played around with Cursors but no
> effect (with Serverside Cursor the execution of the SQL String is faster but
> walking throug the Rows is slower).
>
> The Problem is not the SQL String - the Server takes only 0.2 Seconds for
> the Query but the Transfer of the Dataset is slow. Is RDO or DAO (or
> whatever) faster?
>
> Now some Info about my System:
> ODBC Version: 07.02.0005
> VB 6.0 Enterprise SP5
> W2k Pro SP3
> PostgreSQL 7.2.1 on SuSE Linux (for Development / 7.3 for Production)
>
> My Connectionstring:
> DRIVER={PostgreSQL+ (Beta)};DATABASE=ticket;SERVER=myServer
> ;PORT=5432;UID=myuser;PWD=mypass;ReadOnly=1;Protocol=6.4;FakeOidIndex=1;Show
> OidColumn=1;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socke
> t=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;Com
> mLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLo
> ngVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=d
> d_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=
> 0'
>
> Regards,
>     j.zauner
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

Re: Fastest way to read tuples

From
Simeó Reig
Date:
You should play with a 'limit' in the sql .
Best regards

Simeó Reig



----- Original Message -----
From: "Joachim Zauner" <j.zauner@epcom.cc>
To: <pgsql-odbc@postgresql.org>
Sent: Tuesday, March 25, 2003 9:39 AM
Subject: [ODBC] Fastest way to read tuples


> Hi List,
> I want to retrieve (readonly) 500 - 1500 Tuples via ODBC in an Visual
Basic
> 6 Program over a 64kBit Line. The Raw Data of 500 Tuples is ~ 52kb but it
> takes 7 Seconds to fill the Recordset.
> Currently I use ADO to access the DB. I played around with Cursors but no
> effect (with Serverside Cursor the execution of the SQL String is faster
but
> walking throug the Rows is slower).
>
> The Problem is not the SQL String - the Server takes only 0.2 Seconds for
> the Query but the Transfer of the Dataset is slow. Is RDO or DAO (or
> whatever) faster?
>
> Now some Info about my System:
> ODBC Version: 07.02.0005
> VB 6.0 Enterprise SP5
> W2k Pro SP3
> PostgreSQL 7.2.1 on SuSE Linux (for Development / 7.3 for Production)
>
> My Connectionstring:
> DRIVER={PostgreSQL+ (Beta)};DATABASE=ticket;SERVER=myServer
>
;PORT=5432;UID=myuser;PWD=mypass;ReadOnly=1;Protocol=6.4;FakeOidIndex=1;Show
>
OidColumn=1;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socke
>
t=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;Com
>
mLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLo
>
ngVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=d
>
d_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=
> 0'
>
> Regards,
> j.zauner
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org