[ic] Performance issues with i18n
Thomas Weiss
pater.noster at gmx.net
Thu Dec 4 13:17:22 EST 2003
* 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.
Thomas
------------------------------------------------------------------------
PATCH 1)
========
diff -ru ic.orig/Vend/Table/Shadow.pm ic.new/Vend/Table/Shadow.pm
--- ic.orig/Vend/Table/Shadow.pm Thu Oct 16 00:00:00 2003
+++ ic.new/Vend/Table/Shadow.pm Thu Dec 4 15:39:10 2003
@@ -556,11 +556,9 @@
if (exists $map->{table}) {
$db = Vend::Data::database_exists_ref($map->{table})
or die "unknown table $map->{table} in mapping for column $column of $s->[$TABLE] for locale $locale";
- if ($db->record_exists($key)) {
- $value = $db->field($key, $map->{column});
- } else {
- $value = '';
- }
+
+ $value = $db->field($key, $map->{column});
+ $value = '' unless defined $value;
} else {
$value = $s->[$OBJ]->field($key, $map->{column});
}
PATCH 2)
========
diff -ru ic.orig/Vend/Table/DBI.pm ic.new/Vend/Table/DBI.pm
--- ic.orig/Vend/Table/DBI.pm Fri Aug 22 00:00:00 2003
+++ ic.new/Vend/Table/DBI.pm Thu Dec 4 17:51:47 2003
@@ -1811,11 +1811,11 @@
# Now supported, including qualification
sub each_nokey {
- my $s = shift;
+ my ($s, $qual, $optimap) = @_;
$s = $s->import_db() if ! defined $s->[$DBI];
my ($table, $db, $each);
unless(defined $s->[$EACH]) {
- my $qual = shift || '';
+ $qual ||= '';
$qual .= $s->[$CONFIG]{Export_order}
if $s->[$CONFIG]{Export_order};
($table, $db, $each) = @{$s}[$TABLE,$DBI,$EACH];
@@ -1827,11 +1827,88 @@
! $Global::SuperUserFunction->()
)
) {
- $qual = $qual ? "$qual AND " : 'WHERE ';
+ $qual = $qual ? "$qual AND " : $optimap ? '' : 'WHERE ';
my ($rfield, $rsession) = split /\s*=\s*/, $restrict;
+
+ # Map field name if field has mapping and optimization
+ # was requested.
+ if ($optimap->{$rfield}) {
+ $rfield = "$optimap->{$rfield}->{table}."
+ if $optimap->{$rfield}->{table};
+ $rfield .= "$optimap->{$rfield}->{column}";
+ }
+
$qual .= "$rfield = '$Vend::Session->{$rsession}'";
}
- my $query = $db->prepare("select * from $table " . ($qual || '') )
+
+ my @cols = $s->columns()
+ or die "No column information while trying to optimize query.\n";
+
+ # Build optimized query using joins if optimization data was given.
+ # Used with mapped databases (see Vend::Table::Shadow).
+ my $cols_def = '';
+ my $tables_def = '';
+ my $where_def = '';
+ if ($optimap) {
+ # First remove mapped columns (for single table mappings) from the
+ # column list of this table, as otherwise we would select the same
+ # column twice.
+ my %localmap_cols = map {
+ ( lc($optimap->{$_}->{column}) => 1 )
+ if $optimap->{$_} && !$optimap->{$_}->{table}
+ } @cols;
+ @cols = grep { !$localmap_cols{$_} } @cols;
+
+ my %joined;
+ my $key_col = $s->[$CONFIG]->{KEY} || $cols[0];
+ for my $col (@cols) {
+ my $optimap_col = $optimap->{$col};
+
+ $cols_def .= ', ' unless $cols_def eq '';
+
+ unless ($optimap_col) {
+ $cols_def .= "$table.$col AS $col";
+ next;
+ }
+
+ if ($optimap_col->{table}) {
+ $cols_def .=
+ "$optimap_col->{table}.$optimap_col->{column} AS $col";
+ }
+ else {
+ $cols_def .= "$optimap_col->{column} AS $col";
+ next;
+ }
+
+ next if $joined{$optimap_col->{table}};
+
+ $tables_def .= ', ' unless $tables_def eq '';
+ $tables_def .= $optimap_col->{table};
+
+ my @map_cols = $optimap_col->{db}->columns()
+ or die "No column information while trying to " .
+ "optimize query.\n";
+ my $map_key_col = $optimap_col->{db}->config('KEY')
+ || $map_cols[0];
+
+ $where_def .= ' AND ' unless $where_def eq '';
+
+ $where_def .= "$table.$key_col = " .
+ "$optimap_col->{table}.$map_key_col";
+ $joined{$optimap_col->{table}} = 1;
+ }
+
+ $tables_def = $tables_def ? "$table, $tables_def" : $table;
+ $where_def = $where_def
+ ? "WHERE $where_def" . ($qual ? " AND $qual" : '') : '';
+ }
+ else {
+ $cols_def = '*';
+ $tables_def = $table;
+ $where_def = $qual ? $qual : '';
+ }
+
+ my $query = $db->prepare("select $cols_def from $tables_def $where_def")
or die $s->log_error('prepare');
$query->execute()
or die $s->log_error('execute');
diff -ru ic.orig/Vend/Table/Shadow.pm ic.new/Vend/Table/Shadow.pm
--- ic.orig/Vend/Table/Shadow.pm Thu Oct 16 00:00:00 2003
+++ ic.new/Vend/Table/Shadow.pm Thu Dec 4 17:51:47 2003
@@ -29,8 +29,10 @@
use strict;
-use vars qw($CONFIG $TABLE $KEY $NAME $TYPE $OBJ $PENDING);
-($CONFIG, $TABLE, $KEY, $NAME, $TYPE, $OBJ, $PENDING) = (0 .. 6);
+use vars qw($CONFIG $TABLE $KEY $NAME $TYPE $OBJ $PENDING $EACH_NOKEY);
+($CONFIG, $TABLE, $KEY, $NAME, $TYPE, $OBJ, $PENDING, $EACH_NOKEY) = (0 .. 7);
+
+use constant EACH_NOKEY_OPTIMIZED => 2;
sub config {
my ($s, $key, $value) = @_;
@@ -267,9 +269,27 @@
my $record;
$s = $s->import_db() unless defined $s->[$OBJ];
- if ($record = $s->[$OBJ]->each_nokey($qual)) {
- return $s->_map_array ($record);
+
+ my $optimap;
+ if (!defined $s->[$EACH_NOKEY]) {
+ # first call to each_nokey()
+ $s->[$EACH_NOKEY] = 1;
+
+ # determine if the query is optimizable
+ $optimap = $s->_each_nokey_optimap($qual);
+ $s->[$EACH_NOKEY] |= $s->EACH_NOKEY_OPTIMIZED if $optimap;
+ }
+
+ unless ($record = $s->[$OBJ]->each_nokey($qual, $optimap)) {
+ undef $s->[$EACH_NOKEY];
+ return $record;
}
+
+ # for optimized queries just return the result
+ return $record if $s->[$EACH_NOKEY] & EACH_NOKEY_OPTIMIZED;
+
+ # for non-optimized queries manually map the record and return the result
+ return $s->_map_array($record);
}
sub query {
@@ -454,6 +474,65 @@
}
\%sqlinfo;
+}
+
+# Check if a query can be optimized. If so return a hashref containing
+# relevant data required by the underlying routine to do the actual
+# optimization. Otherwise return undef.
+sub _each_nokey_optimap {
+ my ($s, $qual) = @_;
+
+ # Optimization changes column names due to the joins
+ # it uses, so if there is a qualifier we skip optimization
+ # as we would have to parse it and replace column names
+ # in there otherwise.
+ return undef if $qual;
+
+ # No optimization if shadowing should be skipped.
+ return undef if $::Scratch->{mv_shadowpass};
+
+ # Optimization works for SQL queries only. Skip optimization for
+ # non-SQL databases.
+ return undef if ref $s->[$OBJ] ne 'Vend::Table::DBI';
+
+ my @cols = $s->columns() or return undef;
+ my $map = $s->[$CONFIG]->{MAP};
+ my $locale = $::Scratch->{mv_locale} || 'default';
+
+ my %optimap;
+ for my $col (@cols) {
+ next unless exists $map->{$col}->{$locale};
+
+ my $col_map = $map->{$col}->{$locale};
+
+ # At least another column name has to be given to do the mapping.
+ return undef unless $col_map->{column};
+
+ # Don't optimize if a lookup table was specified or fallback
+ # requested. Fallback would require more database specific features
+ # like LEFT OUTER JOIN and functions like CASE in PostgreSQL or
+ # IF() in MySQL.
+ return undef if $col_map->{lookup_table} || $col_map->{fallback};
+
+ unless ($col_map->{table}) {
+ $optimap{$col} = { column => $col_map->{column} };
+ next;
+ }
+
+ # Skip optimization if the shadow database is not an SQL
+ # database.
+ # TODO: Make sure the database object uses the same DSN as the
+ # shadowed database.
+ my $map_db = Vend::Data::database_exists_ref($col_map->{table});
+ return undef unless ref $map_db eq 'Vend::Table::DBI';
+
+ $optimap{$col} = { table => $col_map->{table},
+ column => $col_map->{column},
+ db => $map_db }
+ }
+
+ return undef unless %optimap;
+ return \%optimap;
}
sub _map_entries {
More information about the interchange-users
mailing list