13.2. Optimizing lists

Interchange has powerful search capabilities that allow you to produce lists of items for use in category lists, product lists, indexes, and other navigation tools.

These are a two-edged sword, though. Lists of hundreds or thousands of entries can be returned, and techniques that work well displaying only a few items may slow to a crawl when a large list is returned.

In general, when you are returning one item (i.e. a flypage) or a small list (i.e. a shopping cart) you can be pretty carefree in your use of [if ...] and [calc] and [perl] tags. When there are hundreds of items, though, you cannot; each complex test or embedded Perl snippet causes the Safe module to have to evaluate code, and each ITL tag requires parsing and argument building.

The Safe module is pretty fast considering what it does, but it can only generate a few thousand instances per second even on a fast system. And the ITL tag parser can likewise only parse thousands of tags per CPU second.

What to do? You want to provide complex conditional tests but you don't want your system to slow to a crawl. Luckily, there are techniques which can speed up complex lists by orders of magnitude.

13.2.1. Benchmarking

A non-precise benchmark of different iteration options can be done with the following global UserTag. Place this in a file in the usertag/ directory in the Interchange root:

UserTag benchmark Order start display
UserTag benchmark AddAttr
UserTag benchmark Routine <<EOR
my $bench_start;
my @bench_times;
sub {
    my ($start, $display, $opt) = @_;
    my @times = times();
    if($start or ! defined $bench_start) {
        $bench_start = 0;
        @bench_times = @times;
        for(@bench_times) {
            $bench_start += $_;
        }
    }
    my $current_total;
    if($display or ! $start) {
        for(@times) {
            $current_total += $_;
        }
        unless ($start) {
            $current_total = sprintf '%.3f', $current_total - $bench_start;
            for(my $i = 0; $i < 4; $i++) {
                $times[$i] = sprintf '%.3f', $times[$i] - $bench_times[$i];
            }
        }
        return $current_total if ! $opt->{verbose};
        return  "total=$current_total user=$times[0] sys=$times[1] " .
                "cuser=$times[2] csys=$times[3]";
    }
    return;
}
EOR

Then at the beginning of the code to check, call

        [benchmark start=1]

to start the measurement. At the end

        [benchmark]

will display the time used. Bear in mind that it is not precise, and that there may be variation due to system conditions. Also, the longer the times and the bigger the list, the better the comparison.

To see the system/user breakdown, do:

        [benchmark verbose=1]

In general, "user" time measures Interchange processing time and and the rest are indicative of the database access overhead, which can vary widely from database to database.

13.2.2. Optimizations

    [loop prefix=foo search="ra=yes"]

        [foo-data products image]
                is slightly faster than
        [foo-field image]
                which is MUCH faster than
        [data products image [foo-code]]
                which is faster than
        [data table=products column=image key="[foo-code]"]

    [/loop]

The loop tags are interpreted by means of fast regular expression scans of the loop container text, and fetch an entire row of data in one query. The [data ...] ITL tag interpretation is delayed until after the loop is finished, whereby the ITL tag parser must find the tag, build a parameter list, then fetch the data with a separate query. If there are repeated references to the same field in the loop, the speedup can be 10x or more.

mv_return_fields (otherwise known as "rf" in one-click terminology) sets the fields that are returned from a search. Once they are returned, they can be accessed with [PREFIX-param field]. They can also be referenced with [PREFIX-pos N], where N is a digit representing the ordinal position (i.e. starting with 0) in the list of fields.

The following are equivalent:

        Benchmark loop-field list: [benchmark start=1]
        <!-- [loop search="ra=yes/st=db"]
                [loop-code] price: [loop-field price] [/loop] -->
        TIME: [benchmark]

        Benchmark loop-param list: [benchmark start=1]
        <!-- [loop search="ra=yes/st=db/rf=sku,price"]
                [loop-code] price: [loop-param price] [/loop] -->
        TIME: [benchmark]

but the second is much, much faster.

A common need when building tables is to conditionally close the table row or data containers. I see a lot of:

        [loop search="ra=yes"]
        [calc] return '<TR>' if [loop-increment] == 1; return[/calc]
        [calc] return '' if [loop-increment] % 3; return '</TR>' [/calc]
        [/loop]

Much faster, by a few orders of magnitude, is:

        [loop search="ra=yes"]
        [loop-change 1][condition]1[/condition]<TR>[/loop-change 1]
        [loop-alternate 3]</TR>[/loop-alternate]
        [/loop]

        If you think you need to close the final row by checking the
        final count, look at this:

        [loop search="ra=yes"]
        [on-match]
                <TABLE>
                <TR>
        [/on-match]

        [list]
                        <TD>[loop-code]</TD>
                [loop-alternate 3]</TR><TR>[/loop-alternate]
        [/list]

        [on-match]
                </TR>
                </TABLE>
        [/on-match]

        [no-match]
                No match, sorry.
        [/no-match]

[/loop]

This is a hundred times faster than anything you can build with multiple [calc] tags.

Consider these two snippets:

        [if scratch|value|cgi key] THEN [/if]

and:

        [if scratch|value|cgi key == '1'] THEN [/if]

The first one doesn't require Perl evaluation. It simply checks to see if the value is blank or 0, and returns true if it is anything but. Of course this requires setting your test values to blank or 0 instead of "No" or " " or somesuch, but it is anywhere from 20-35% faster.

Try it on the foundation demo:

    ---- begin test ---

    Overhead:
    [benchmark start=1]
        <!-- [loop search="ra=yes"]
                    [set cert][loop-field gift_cert][/set]
            [/loop] -->
    [benchmark]
    <P>


    if scratch compare:
    [benchmark start=1]
        <!--
        [loop search="ra=yes"]
        [set cert][loop-field gift_cert][/set]
        [loop-code] [if scratch cert] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert] YES [else] NO [/else][/if]
        [/loop]
        -->

    [benchmark]
    <P>

    if scratch compare eq 1:
    [benchmark start=1]
        <!--
        [loop search="ra=yes"]
        [set cert][loop-field gift_cert][/set]
        [loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
        [loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
        [/loop]
        -->
    [benchmark]
    <P>

    [page @@MV_PAGE@@]Again</a>

    ---- end test ---

You can execute the same code as [calc] with [PREFIX-calc], which has two benefits:

  1. It doesn't require ITL parsing.
  2. It is executed during the loop instead of after it.

The [PREFIX-calc] object has complete access to all normal embedded Perl objects like $Values, $Carts, $Tag, and such. If you want to make a data table (i.e. "products" or "pricing") available for access inside of it, just do:

        [perl tables="products pricing"] [/perl]

prior to list start. Now you can do something like:

    [loop search="ra=yes"]
        [loop-calc]
            $desc = $Tag->data('products', 'description', '[loop-code]');
            $link = $Tag->page('[loop-code]');
            return "$link $desc </A>";
        [/loop-calc] <BR>
    [/loop]

For repetitive routines, you can achieve a considerable savings in CPU by pre-compiling your embedded Perl code.

In the "Construct Something" demo, the bar_link() routine in catalog_before.cfg is an example of compiling the subroutine once at catalog configuration time.

You can also compile routines at the time of the list execution with [item-sub routine] CODE [/item-sub]. This means only one Safe evaluation is done -- every time the [loop-exec routine] is called, it is done fast as a call to the routine. This can be 10 times or more faster than separate [calc] calls, or 5 times faster than separate [PREFIX-calc] calls.

    [benchmark start=1]
    loop-calc:
      <!--
            [loop search="st=db/fi=country/ra=yes/ml=1000"]
            [loop-calc]
                    my $code = q{[loop-code]};
                    return "code '$code' reversed is " . reverse($code);
            [/loop-calc]
            [/loop]
      -->

    [benchmark]

    <P>

    [benchmark start=1]
    loop-sub and loop-exec:
      <!--
            [loop search="st=db/fi=country/ra=yes/ml=1000"]
            [loop-sub country_compare]
                    my $code = shift;
                    return "code '$code' reversed is " . reverse($code);
            [/loop-sub]
            [loop-exec country_compare][loop-code][/loop-exec]
            [/loop]
      -->

    [benchmark]

You can run [query arrayref=myref sql="query"], which saves the results of the search/query in a Perl reference. It is then available in $Tmp->{myref}. (Of course, "myref" can be any arbitrary name.)

This is the fastest possible method to display a list.

  --- begin test code ---
  [set waiting_for]os28004[/set]

  [benchmark start=1] Embedded Perl
  <!--
  [query arrayref=myref sql="select sku,price,description from products"]
        <!-- make query, this container text is not used. -->
  [/query]

  [perl]
    # Get the query results, has multiple fields
    my $ary = $Tmp->{myref};
    my $out = '';
    foreach $line (@$ary) {
        my ($sku, $price, $desc) = @$line;
        if($sku eq $Scratch->{waiting_for}) {
                $out .= "We were waiting for this one!!!!\n";
        }
        $out .= "sku: $sku price: $price description: $desc\n";
    }
    return $out;
  [/perl]
  -->
  TIME: [benchmark]

  [benchmark start=1] All loop
  <!--
  [query list=1 sql="select sku,price,description from products"]
        [if scratch waiting_for eq '[sql-code]']
            We were waiting for this one!!!!
        [/if]
        sku: [sql-code]
        price: [sql-param price]
        desc: [sql-param description]
  [/query]
  -->

  TIME: [benchmark]

  --- end test code ---