loading CALCULATED columns into a list read from a database - was interesting optimization

[Doug Easterbrook]

hi Kelly, Michael, Michael, et al.

this is a very interesting question that, as Michael Monschau point  
out, we spent hours at Euromnis working on the ins and outs of things  
and shared the findings with Bob Whiting and Bob Mitchell.

In the end, I had a different request of engineering (from Michaels)  
in that the $fetch method would get an extra parameter as in

do $cinst.$fetch(listOrRow,fetchcount,append,$cinst.$MyCallBack)

and $mycallback would give me a change to deal with all calculated  
columns in the list before they are loaded into the array.  Thus, as  
Kelly wants, I could calculate a full name based on con(FirstName,'  
',lastName) with no performance penalty.



what we've found does best in optimizing the list building:

do list.$definefromsqlclass('tableClass')

Note: table class can use a query that has the extra variables defined  
from a file class or what have you  -or-

do list.$add('extra column')

We often add a fair number of columns for display purposes rather than  
use the raw data.  The more columns you add, the slower list building  
goes when you calculate the columns using $cinst.



so, for performance, we now sometimes do this:


1) fetch all data into a TEMPORARY list without calculating the  
additional columns
2) loop the temporary list using a for loop (faster than whiles)

begin reversible block
set current list  'tempList'
end reversible block

calc FetchString as 'list.$add(col1,col2,col3)'

for all lines in list
     load from list
     calc tempVar as MyCalculation
     do evalf(FetchString)
end for




the key in al this is:
a) using set current list and a for loop is faster
b) the load from list gets everything into the current record buffer  
(we have file vars that are defined the same as our schema vars, so we  
are cool in that respect
c) the variable 'fetchlist' is something to simply add all the data  
into the proper list
d) the tempvars are calculated using Current Record Buffer  
variables...   not $cinst.tempvar.    This is much faster
e) and the evalf (fetchstring) tokenizes things to make it fast.



funny, but with the way $fetch works getting data into the list and  
the subsequent calculations causing a whole lot of data moving around  
in memory as the list gets adjusted row by row.....

simply tricking omnis and using two lists is a fair bit faster.


theres a trick for all.



hopefully, either Michael's suggestion or mine to act on the CRB  
during the loading of each row sometime between the actual fetch and  
the sticking of the data into the list .... will avoid the list  
compaction.    My notion is that I get a callback direct from the dam  
with the raw data so I can optimize calculated columns.


So thats what I've noticed works.  In our tests on larger lists, we  
sometimes saw 50% or so improvement (i.e. twice as fast) as what seems  
like the more direct approach of calculating a variable in the loop...


just two cents.  hope this helps others


Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 536-1205    Fax (403) 536-1210


On May 15,2008, at 10:20 AM, omnisdev-en-request at lists.omnis-dev.com  
wrote:

> One really good enhancement would be (Bob are you listening), if
> after one defined a list, prior to building the list, one could
> assign an initialisation method that is called for each row by Omnis
> as the list is build. This could be a method call in the current
> context, for example.
>
> Do MyList.$define(...)
> 	or
> Do MyList.$definefromsqlclass(...)
> 	followed by
> Do MyList.$initcalc.$assign('$cinst.$initmylist')
>
> $initmylist would have one Field Reference or Item reference
> parameter that points to the row that has just been build. The method
> can then initialise the special columns. Our tests showed that the
> overhead of calling the method is negligible compared to the overhead
> of initialising columns after a list has been build.