[ic] Performance issues with i18n

[Mike Heins]

Quoting Thomas Weiss (pater.noster at gmx.net):
> * On Wed, Dec 03 panos wrote:
> > I'm building an internationalized catalog with Interchange 4.9.9 and the
> > locale based database mappings as found in the foundation catalog. My
> > problem is that as soon as the mappings are used (by using a locale
> > other than the default) everything gets terribly slow. The reason is the
> > way Vend::Table::Shadow applies the mappings when querying the database.
> > 
> > For example when using the foundation catalog's data and database mapping
> > settings it takes about 80 sql queries to build the category menu (with
> > the category_vertical component) when using the default locale (no
> > mappings in use, no Vend::Table::Shadow), but with a non-default locale
> > in use it takes about 950 sql queries to do the same, which is IMHO way
> > too much to be efficient.
> 
> I've been hacking around a little bit and have been able to speed up the
> database mapping for this case by more than factor 3. The benchmarks
> have been done with the foundation catalog's process page with
> category_vertical being the only enabled component. Always a non-default
> locale setting was used. Interchange was running on my workstation, a
> PC with a P4 1.5GHz CPU and 512MB and the database server (PostgreSQL)
> was running on a machine with a PIII 550GHz CPU with 768MB RAM.
> 
> The number of database queries required to display the page has dropped
> from about 950 down to about 120 which raises the number of pages
> Interchange can server in a minute from 28 up to 95. When using single
> table mappings (i.e. the mapped column is in the same table as the
> original column) it served 106 pages in 1 minute.
> 
> The optimizations made were 1) omitting an unnessessary call to
> record_exists() in Vend::Table::Shadow and 2) letting the database
> server do the hard work applying the mappings instead of doing it
> manually.
> 
> Below are patches against Vend::Table::DBI (CVS Version 2.57) and
> Vend::Table::Shadow (CVS Version 1.43) which implement these
> optimizations. The patches are meant as a proof-of-concept kind of thing
> and there is of course room for improvement. The most important thing
> to do is support optimization even if the fallback option is set for
> the mapping. Currently optimization is done only if fallback is off
> but it can relatively easy be implemented (see patch no. 2 for details).
> 
> I'm looking forward to hear any opinions.

This optimization effort is welcome. However, as a real means of optimzing
I recommend looking at timed-build. Just wrapping the category display
in 

	[timed-build login=1 file="tmp/category_[scratch mv_locale]" minutes=10]
	(the ITL for category display)
	[/timed-build]

should remove the necessity to pay too much attention to this. Of course
it will not work for users who don't accept cookies, but that should
be a small percentage.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295      <mike at perusion.com>

Friends don't let friends use Outlook. -- Bob Blaylock