[ic] Performance issues with i18n

Mike Heins mike at perusion.com
Thu Dec 4 14:04:36 EST 2003

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

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

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

More information about the interchange-users mailing list