Area: Core
Category: Templates
Item: List optimization
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.
------------
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.
-------------
Optimizations
-------------
----------------------------------------------------
[PREFIX-tag] is faster than [parsed-tag]
[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.
----------------------------------------------------
Pre-fetch data with rf=field1,field2,field3 and access
with [PREFIX-param field1].
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]
<P>
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.
----------------------------------------------------
[PREFIX-alternate N] is available for row counting and display.
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.
----------------------------------------------------
Use simple go/nogo comparisons in [if ...]
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 construct 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[/page]
---- end test ---
----------------------------------------------------
Use [PREFIX-calc] instead of [calc] or [perl]
(This facility is not yet documented, but will be soon.)
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]
----------------------------------------------------
ADVANCED: Precompile and execute with [PREFIX-sub] and [PREFIX-exec]
(This facility is not yet documented, but will be soon.)
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.
Example:
[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]
----------------------------------------------------
ADVANCED: Execute and save with [query ...], then use an embedded Perl
routine.
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.
Observe:
--- 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 ---
----------------------------------------------------
Other things that help:
*** Avoid interpolate=1 when possible. A separate tag parser must be spawned
every time you do this. Many times people use this without needing it.
*** Avoid saving large values to Scratch, as these have to be written to
the users session. If you need them only for the current page, clear
at the end by using [tmp scratch_var] contents [/tmp], which is
the same as [seti scratch_var] contents [/seti] except clears the
value before the session is written. You can also use
[scratchd scratch_var] to return the contents and delete them from
the session at the same time.
*** Use the [more-list] facility to break up your large searches. You
can use them in [query ....] and [loop ...] searches as well -- see
the docs.
--
Akopia, Inc., 131 Willow Lane, Floor 2, Oxford, OH 45056
phone +1.513.523.7621 fax 7501 <heins@akopia.com>
Any man who is under 30, and is not liberal, has not heart; and any man
who is over 30, and is not a conservative, has not brains.
-- Winston Churchill