[Date Prev][Date Next][Thread Prev][Thread Next][Interchange by date
][Interchange by thread
]
RE: [ic] Advanced search on large DB
> I have an arbitary database of over 100000 products in a new
> 4.8.2 store running mySQL on a modest server. What I need to
> put together is an advanced search form to allow customers to
> search titles for keywords within prod_group or category.
>
> The data is not unlike the original 'Art gallery' type
> database: description, artist, prod_group, category.
>
> What I really need is an example of an efficient advanced
> search form that deals directly with the mySQL DB allowing
> searches through hardcoded prod_groups or categories drop downs.
>
> My SQL (no pun intended) understanding is not that brilliant.
> My previous catalogs relied on Glimpse for searching.
>
> I've been through
> http://www.minivend.com/doc-4.8/frames/ictags_> 59.html
> several
> times and haven't been able to grasp the
> concepts as much as I would have liked.
>
> How do I allow searches through a DB this large without over
> burdening the server?
>
> I would love to view an example or two of an advanced search
> page specific to mySQL and Interchange and a large DB.
>
> Any other tips would be tremendously appreciated!
>
> Simon
Below is a message that Mike Heins posted circa 12 months ago.
Dan Browning
Kavod Technologies
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
_______________________________________________
interchange-users mailing list
interchange-users@interchange.redhat.com
http://interchange.redhat.com/mailman/listinfo/interchange-users