SQL Anywhere Mobile - Ultralite DB: Fastest way to loop through table ?

Author

Ultralite DB: Fastest way to loop through table ?

handheldmaster

2005-06-02, 1:24 pm

ULTRALITE FORUM DOES NOT ALLOW TO CREATE A NEW TOPIC HENCE
CROSS-POSTING INTO MOBILINK.

Using Ultralite 8.0.2 (4392) - MobileVB 3.5 (0633).

If one does something like this where myTable is a table
with around 5200 records the code takes around 15 seconds to
loop through the recordset.

myTable.MoveFirstFor intCounter = 1 to myTable.RowCount
'some basic IF statements for conditional checks.
myTable.MoveNextnext intCounter

Any way to improve this ? Cannot use FindBegin, FindFirst as
business logic requires to loop through entire table to get
out a collection of records that match a specific criteria.

Any inputs appreciated ... are others seeing such slow
downs.

Tom Slee

2005-06-15, 8:25 pm

I answered this in the AppForge Forums. Here is the answer again in case
you did not see it there.

My guess is that for big tables mTable.RowCount is pretty slow, so I
would try this to avoid re-evaluating it each time:

rc = myTable.RowCount
myTable.MoveFirst
For intCounter = 1 to rc
'some basic IF statements for conditional checks.
myTable.MoveNext
next intCounter

Alternatively, upgrade to version 9 and you can let UltraLite do the
loop by executing SELECT * FROM <table> WHERE <condition>

Tom

handheldmaster wrote:
> ULTRALITE FORUM DOES NOT ALLOW TO CREATE A NEW TOPIC HENCE
> CROSS-POSTING INTO MOBILINK.
>
> Using Ultralite 8.0.2 (4392) - MobileVB 3.5 (0633).
>
> If one does something like this where myTable is a table
> with around 5200 records the code takes around 15 seconds to
> loop through the recordset.
>
>
> myTable.MoveFirstFor intCounter = 1 to myTable.RowCount
> 'some basic IF statements for conditional checks.
> myTable.MoveNextnext intCounter
>
> Any way to improve this ? Cannot use FindBegin, FindFirst as
> business logic requires to loop through entire table to get
> out a collection of records that match a specific criteria.
>
> Any inputs appreciated ... are others seeing such slow
> downs.

Tom Slee

2005-06-16, 9:23 am

A follow up to the "fast looping" issue with some input from my colleagues.

1. A fast way to loop thru the rows of a table with the table API is
to check the return code on MoveNext. MoveNext returns False when the
table loop is done.

t.MoveFirst
Dim found As Boolean
found = False
While t.MoveNext && Not found
..if row matches, set found to True for early loop exit..
Wend

2. In addition, if you want to tune performance further, accessing
columns by name (as in Table.Column( <name> )is a slow operation. You
may want to cache the Column object by saving it at the beginning of the
loop.

3. By the way, the UltraLite forums are working fine - there should be
no problem in posting to them.

Tom Slee
iAnywhere Product Management

Tom Slee wrote:[color=darkred
]
> I answered this in the AppForge Forums. Here is the answer again in case
> you did not see it there.
>
> My guess is that for big tables mTable.RowCount is pretty slow, so I
> would try this to avoid re-evaluating it each time:
>
> rc = myTable.RowCount
> myTable.MoveFirst
> For intCounter = 1 to rc
> 'some basic IF statements for conditional checks.
> myTable.MoveNext
> next intCounter
>
> Alternatively, upgrade to version 9 and you can let UltraLite do the
> loop by executing SELECT * FROM <table> WHERE <condition>
>
> Tom
>
> handheldmaster wrote:
>

handheldmaster

2005-06-16, 9:23 am

Thanks for the response. Did not see it on the appforge
forum - luckily caught it here.

> 1. A fast way to loop thru the rows of a table with the
> table API is to check the return code on MoveNext.
> MoveNext returns False when the table loop is done.
>
> t.MoveFirst
> Dim found As Boolean
> found = False
> While t.MoveNext && Not found
> ..if row matches, set found to True for early loop
> exit.. Wend
>
This is not very different than the For Loop.. Currently I
use "Exit For" if condition is met .. and in few cases I
need to loop through each record to gather computational
statistics.

> 2. In addition, if you want to tune performance further,
> accessing columns by name (as in Table.Column( <name> )is
> a slow operation. You may want to cache the Column object
> by saving it at the beginning of the loop.
>
Will try this .. So a ULColumn object for each column.

> 3. By the way, the UltraLite forums are working fine -
> there should be no problem in posting to them.
>
Ok. Did not work on Jun 2nd these do today ..

Sponsored Links