[ic] Misc Questions
Dan Browning
interchange-users@lists.akopia.com
Tue Jul 17 19:12:00 2001
--=====================_84100099==_
Content-Type: text/plain; charset="us-ascii"; format=flowed
At 01:56 PM 7/17/2001 -0400, you wrote:
>Hello All,
> I'm in the process of building a site, and I have a few
> questions, that I couldn't find answers that would help me on the mailing
> list archives, or the docs.
>
>Here's my setup. RaQXTR -- 933MHz PIII, 256RAM, IC 4.7.5, raid5. Which
>connects (VIA 100MB switch) to a postgres 7.0.2 db server that has a 733
>MHz PIII, 512RAM, raid5, etc. My products file has just under 1000 items
>(955 to be exact) and a zip_zone table that has 963 entries. I have
>created index's for code, zones columns on the products table, and an
>index for zone on the zip_zone table.
>
>I have to perform a complex search, and I have working SQL code for either
>a sub-query, or a JOIN type search. Both work.
>
>Here's my code:
>
>[comment]
>[query list=1 st=db more=1 ml=50 sql=|
> SELECT products.code, products.name, products.short_desc,
> products.price, products.qty, products.new_plant, products.avail
> FROM products, zip_zone
> WHERE zip_zone.zip = '[value search_zip]'
> AND products.zones ~ zip_zone.zone
> ORDER BY products.name;
> |
>]
>[/comment]
>
>[query list=1 more=1 ml=50 st=db sql=|
> SELECT code, name, short_desc, price, qty, new_plant
> FROM products
> WHERE zones ~ (
> SELECT zone from zip_zone
> WHERE zip = '[value search_zip]');
> |
>]
>
>[list]
><snip html taken out>
>[sql-param code] [sql-param name] etc.
></snip>
>[/list]
>[more-list]
>[more]
>[/more-list]
>[/query]
>
>Questions:
>
>1) This search takes anywhere from 60 sec to over 2 minutes to return the
>page. Is there something that I'm missing that can speed this up? Do I
>need to add something like "DATABASE products INDEX code" to the catalog.cfg?
Sounds like a Database optimization question to me. The INDEX directive
only (IIRC) creates your indecies for you on import, which you have done
already with the CREATE INDEX command.
Run your query directly from Postgresql and report the time it takes
there. If there is a big discrepancy between that and IC (allowing for
network/dbi/ic overhead) then you might have something. But if the query
is also slow when run directly from your database box (using psql), then
you might try the PostgreSQL list for speed optimization tips (see 1 below).
But also look at the attached email posted by Mike Hiens a while back
regarding speed optimization. Your biggest optimization would be to do a
[timed-build] of the entire block, set to every 20 minutes or so, if that
were possible by the nature of the data.
>I have created index's for code, zones columns on the products table, and
>an index for zone on the zip_zone table.
Did you index your where clauses? E.g., zip in zip_zone?
>1a) I added a more list, which increased display time, BUT
>
>2) The More List displays at the top of the table, is there a way to make
>it appear at just the bottom? Or at both top AND bottom of the table?
For me, it works at both the top and bottom, if that is what you are asking.
top
[more-list]
Matches [matches] of [match-count] found.
<BR>[more]<BR>
[/more-list]
middle
[more-list]
Matches [matches] of [match-count] found.
<BR>[more]<BR>
[/more-list]
bottom
Anyway, good luck squeezing more speed out of it. You might also try
upgrading your operating system (2.4.x), database (pgsql 7.1), DBI/DBD
modules, and IC (4.7.6) if you haven't already.
HTH,
Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com
--=====================_84100099==_
Content-Type: text/plain; charset="us-ascii"
Content-Disposition: attachment; filename="ic Tips and Tricks -- optimizing lists.txt"
From: interchange-users-admin@minivend.com on behalf of Mike Heins
[mikeh@minivend.com]
Sent: Tuesday, November 21, 2000 12:58 PM
To: Interchange User List
Subject: [ic] Tips and Tricks -- optimizing lists
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
_______________________________________________
Interchange-users mailing list
Interchange-users@www.minivend.com
http://www.minivend.com/mailman/listinfo/interchange-users
--=====================_84100099==_--