Re: Does the Number of Fields in Products Effect Performance

[mikeh@minivend.com]

******    message to minivend-users from mikeh@minivend.com     ******

Quoting Christopher Thompson (Thompson-Jordan@mindspring.com):
> 
> I'm using GDBM with a 1000-2000 products. My product database has a large
> number of fields (50+). Most of the fields are only displayed on flypage.
> Only the first 8 fields are used my most searches.
> 
> - Does having a large number of fields affect search performance?
> 

Slightly, but not too much unless the character count is very
high. It is affected more if you are in the habit of selecting
more than just the default 'code' as the return field, or if you
do field-specific searches on more than one field where there
is a frequent regular expression match as the main qualifer.

By qualifier, I mean that each line is scanned to match any regular
expression that is supplied. Something like sf=category/se=Renaissance,
for example. If Renaissance is not in the line, we go to the next line
and are done with it. If you do something like:

    [page href=scan arg="
                        co=yes

                        sf=price
                        op=>
                        se=100
                        nu=yes

                        sf=price
                        op=<
                        se=1000
                        nu=yes

                        "]

prepare for slowness, as each line must be pulled apart and tested.

The more unique your search strings are the better search performance
will be.

> - If I mainly the first few fields, is performance the same no matter
> how many additional fields I have?

Not really. It does take extra time to pack/unpack the record; this is
somewhat offset by only having to open and acccess one table if your
data would otherwise be spread (within a search list) across multiple
tables. If most of your accesses are with [item-field ...] and you have
only one ProductFiles (one named "products" is the default) then a whole
row is made available pretty quickly.

As I frequently say, the search is done in a very small amount of
time on a file of that size. The database accesses are what take more
time, and it may pay to break out the infrequently-used data. The
difference will probably not be huge.

> 
> I imagine the database has a table of record offsets, so the speed in
> selecting a record is not affected by the number of fields. But, I really
> don't know. If it does affect performance, I might put those additional
> fields in a separate database.
>

No record offsets; it is a tied hash which is also very fast. But the 
searching is all done on the ASCII file, and number of lines has a 
far greater effect on performance than line size.

Bottom line is that the number of conditionals, calcs, and other things
like [on-change] is the biggest page display performance drain.

Anything which calls a conditional besides just checking for
a non-empty non-blank value uses the Safe reval() function.

This is fast:

    [if-field whatever]something[/if-field]

so is this

    [if value whatever]
    ...
    [/if]

this is much slower
    
    [if value whatever eq 'Yes']

and this is slower yet

    [if data products::price::[item-code] > 0]
    ...
    [/if]

and [perl ....] takes up the rear when called many times. (You might
notice that I use [calc] ... [/calc] a lot; it has less overhead.) Yet
[perl] can be very good if you use it to parse and build your lists. It
is the call that has the overhead -- the code itself runs fast, and if
it is only called once to display a whole page it is the fastest way to
build large complex lists. My record is a page that took over 3 minutes
to display with lots of embedded conditionals -- it accessed thousands
of data items with thousands of conditionals. The [perl ...] tag applied
to the whole list cut display time down to less than 2 seconds.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
Few blame themselves until they     131 Willow Lane, Floor 2  | ||  _ \
have exhausted all other            Oxford, OH  45056         | || |_) |
possibilities.                      <mikeh@minivend.com>     |___|  _ <
 -- anonymous                       513.523.7621 FAX 7501        |_| \_\
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list