Re: [WWdevel] relative speed of sql_single and gdbm

[Arnold P. <ap...@ma...>]

At 02:06 PM 3/10/2005, John Jones wrote:

Hi John,

Thanks very much for the patch. I put it in and tested it a little bit. I 
haven't seen any problems but also I don't see a great deal of improvement 
in speed.   I just tested it on one problem inserting correct and incorrect 
answers.  With the original SQL.pm it took 8 seconds (I only did one 
attempt) and with the new SQL.pm between 2 and 8 seconds (details 
below).   The load avg. on the server was about .35 and mysql cpu usage was 
about 30 or 40%.  I guess we will have to wait until my next assignment is 
due to see if this makes a difference but the times below are still way too 
long.

Arnie

using original SQL.pm
[Wed Mar 16 14:03:12 2005] 42868 1110999792 - [/webwork2/mth162/7/1/] 
[runTime = 8.0 sec sql_single]

using new SQL.pm
[Wed Mar 16 14:27:14 2005] 53844 1111001234 - [/webwork2/mth162/7/1/] 
[runTime = 5.0 sec sql_single]
[Wed Mar 16 14:27:44 2005] 54209 1111001264 - [/webwork2/mth162/7/1/] 
[runTime = 7.0 sec sql_single]
[Wed Mar 16 14:28:00 2005] 54209 1111001280 - [/webwork2/mth162/7/1/] 
[runTime = 6.0 sec sql_single]
[Wed Mar 16 14:28:18 2005] 53850 1111001298 - [/webwork2/mth162/7/1/] 
[runTime = 2.0 sec sql_single]
[Wed Mar 16 14:28:27 2005] 54434 1111001307 - [/webwork2/mth162/7/1/] 
[runTime = 3.0 sec sql_single]
[Wed Mar 16 14:28:37 2005] 54434 1111001317 - [/webwork2/mth162/7/1/] 
[runTime = 3.0 sec sql_single]
[Wed Mar 16 14:28:45 2005] 54434 1111001325 - [/webwork2/mth162/7/1/] 
[runTime = 3.0 sec sql_single]
[Wed Mar 16 14:30:43 2005] 53844 1111001443 - [/webwork2/mth162/7/1/] 
[runTime = 6.0 sec sql_single]
[Wed Mar 16 14:30:56 2005] 53844 1111001456 - [/webwork2/mth162/7/1/] 
[runTime = 6.0 sec sql_single]
[Wed Mar 16 14:31:05 2005] 53844 1111001465 - [/webwork2/mth162/7/1/] 
[runTime = 6.0 sec sql_single]
[Wed Mar 16 14:31:22 2005] 53844 1111001482 - [/webwork2/mth162/7/1/] 
[runTime = 6.0 sec sql_single]
[Wed Mar 16 14:34:10 2005] 53850 1111001650 - [/webwork2/mth162/7/1/] 
[runTime = 8.0 sec sql_single]
[Wed Mar 16 14:35:44 2005] 54964 1111001744 - [/webwork2/mth162/7/1/] 
[runTime = 5.0 sec sql_single]
[Wed Mar 16 14:44:09 2005] 55114 1111002249 - [/webwork2/mth162/7/1/] 
[runTime = 2.0 sec sql_single]

>Hi,
>
>In a previous e-mail I had found that the slowdown was due to 
>case-sensitivity in the mysql index.  The index is not case sensitive, so 
>if you force key elements to treated as case sensitive, then the index is 
>ignored and the whole thing slows to a crawl with big databases.
>
>Attached is a version of lib/WeBWorK/DB/Schema/SQL.pm which tries to 
>adjust for this.  If you are making a deletion of a database record, or 
>putting something into the database, then those calls have to use case 
>sensitive key elements, so they will remain slow.  For select statements, 
>the call can be not case sensitive, and then we filter out the records 
>which we really want.
>
>I have tested this, but it would be better if other people tested it 
>too.  Unlike changes to other files, an error here can mess up an existing 
>course.
>
>I thought about other ways to handle this.  One I mentioned before was to 
>try to change the data type of the key fields so that the indexing might 
>end up case sensitive.  I haven't found a way to do that yet.
>
>If the fact that inserting and deleting information from the database is 
>still slow is a problem, then another possibility would be to add a unique 
>numeric key to each table.  Then when you want to delete rows from a 
>table, you select (case insensitively), pick out the ones which match your 
>favorite key fields (including case), then use the unique id number for 
>those rows in the actual deletion statement.  A similar strategy might 
>work for put type statements.
>
>John
>
>
>
>################################################################################
># WeBWorK Online Homework Delivery System
># Copyright ) 2000-2003 The WeBWorK Project, http://openwebwork.sf.net/
># $CVSHeader: webwork-modperl/lib/WeBWorK/DB/Schema/SQL.pm,v 1.24 
>2004/10/22 22:59:52 sh002i Exp $
>#
># This program is free software; you can redistribute it and/or modify it 
>under
># the terms of either: (a) the GNU General Public License as published by the
># Free Software Foundation; either version 2, or (at your option) any later
># version, or (b) the "Artistic License" which comes with this package.
>#
># This program is distributed in the hope that it will be useful, but WITHOUT
># ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
>FITNESS
># FOR A PARTICULAR PURPOSE.  See either the GNU General Public License or the
># Artistic License for more details.
>################################################################################
>
>package WeBWorK::DB::Schema::SQL;
>use base qw(WeBWorK::DB::Schema);
>
>=head1 NAME
>
>WeBWorK::DB::Schema::SQL - support SQL access to all tables.
>
>=cut
>
>use strict;
>use warnings;
>use Carp qw(croak);
>use Date::Format;
>
>use constant TABLES => qw(*);
>use constant STYLE  => "dbi";
>
>=head1 SUPPORTED PARAMS
>
>This schema pays attention to the following items in the C<params> entry.
>
>=over
>
>=item tableOverride
>
>Alternate name for this table, to satisfy SQL naming requirements.
>
>=item fieldOverride
>
>A reference to a hash mapping field names to alternate names, to satisfy SQL
>naming requirements.
>
>=back
>
>=cut
>
>
>################################################################################
># constructor for SQL-specific behavior
>################################################################################
>
>sub new {
>         my ($proto, $db, $driver, $table, $record, $params) = @_;
>         my $self = $proto->SUPER::new($db, $driver, $table, $record, 
> $params);
>
>         ## override table name if tableOverride param is given
>         #$self->{table} = $params->{tableOverride} if 
> $params->{tableOverride};
>
>         # add sqlTable field
>         $self->{sqlTable} = $params->{tableOverride} || $self->{table};
>
>         return $self;
>}
>
>################################################################################
># table access functions
>################################################################################
>
>sub count {
>         my ($self, @keyparts) = @_;
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @keynames = $self->sqlKeynames();
>
>         croak "too many keyparts for table $table (need at most: @keynames)"
>                 if @keyparts > @keynames;
>
>         my ($where, @where_args) = $self->makeWhereClause(0, @keyparts);
>
>         my $stmt = "SELECT * FROM `$sqlTable` $where";
>         $self->debug("SQL-count: $stmt\n");
>
>         $self->{driver}->connect("ro");
>
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         $sth->execute(@where_args);
>         my ($result) = $sth->fetchall_arrayref;
>
>         $self->{driver}->disconnect();
>my @arr_res = case_check($result, @keyparts);
>
>         return scalar(@arr_res);
>}
>
>sub list($@) {
>         my ($self, @keyparts) = @_;
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @keynames = $self->sqlKeynames();
>         my $keynames = join(", ", @keynames);
>
>         croak "too many keyparts for table $table (need at most: @keynames)"
>                 if @keyparts > @keynames;
>
>         my ($where, @where_args) = $self->makeWhereClause(0, @keyparts);
>
>         my $stmt = "SELECT $keynames FROM `$sqlTable` $where";
>         $self->debug("SQL-list: $stmt\n");
>
>         $self->{driver}->connect("ro");
>
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         $sth->execute(@where_args);
>         my $result = $sth->fetchall_arrayref;
>
>         $self->{driver}->disconnect();
>
>         croak "failed to SELECT: $DBI::errstr" unless defined $result;
>my @arr_res = case_check($result, @keyparts);
>         return @arr_res;
>}
>
>sub exists($@) {
>         my ($self, @keyparts) = @_;
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @keynames = $self->sqlKeynames();
>
>         croak "wrong number of keyparts for table $table (needs: @keynames)"
>                 unless @keyparts == @keynames;
>
>         my ($where, @where_args) = $self->makeWhereClause(0, @keyparts);
>
>         my $stmt = "SELECT * FROM `$sqlTable` $where";
>         $self->debug("SQL-exists: $stmt\n");
>
>         $self->{driver}->connect("ro");
>
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         $sth->execute(@where_args);
>         my ($result) = $sth->fetchall_arrayref;
>
>         $self->{driver}->disconnect();
>
>         croak "failed to SELECT: $DBI::errstr" unless defined $result;
>
>my @arr_res = case_check($result, @keyparts);
>         return scalar(@arr_res) > 0;
>}
>
>sub add($$) {
>         my ($self, $Record) = @_;
>
>         my @realKeynames = $self->{record}->KEYFIELDS();
>         my @keyparts = map { $Record->$_() } @realKeynames;
>         croak "(" . join(", ", @keyparts) . "): exists (use put)"
>                 if $self->exists(@keyparts);
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @fieldnames = $self->sqlFieldnames();
>         my $fieldnames = join(", ", @fieldnames);
>         my $marks = join(", ", map { "?" } @fieldnames);
>
>         my @realFieldnames = $self->{record}->FIELDS();
>         my @fieldvalues = map { $Record->$_() } @realFieldnames;
>
>         my $stmt = "INSERT INTO `$sqlTable` ($fieldnames) VALUES ($marks)";
>         $self->debug("SQL-add: $stmt\n");
>
>         $self->{driver}->connect("rw");
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         my $result = $sth->execute(@fieldvalues);
>         $self->{driver}->disconnect();
>
>         unless (defined $result) {
>                 my @realKeynames = $self->{record}->KEYFIELDS();
>                 my @keyvalues = map { $Record->$_() } @realKeynames;
>                 croak "(" . join(", ", @keyvalues) . "): failed to 
> INSERT: $DBI::errstr";
>         }
>
>         return 1;
>}
>
>sub get($@) {
>         my ($self, @keyparts) = @_;
>
>         return ($self->gets(\@keyparts))[0];
>}
>
>sub gets($@) {
>         my ($self, @keypartsRefList) = @_;
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @keynames = $self->sqlKeynames();
>
>         my @records;
>         $self->{driver}->connect("ro");
>         foreach my $keypartsRef (@keypartsRefList) {
>                 my @keyparts = @$keypartsRef;
>
>                 croak "wrong number of keyparts for table $table (needs: 
> @keynames)"
>                         unless @keyparts == @keynames;
>
>                 my ($where, @where_args) = $self->makeWhereClause(0, 
> @keyparts);
>
>                 my $stmt = "SELECT * FROM `$sqlTable` $where";
>                 $self->debug("SQL-gets: $stmt\n");
>
>                 my $sth = $self->{driver}->dbi()->prepare($stmt);
>                 $sth->execute(@where_args);
>                 my $result = $sth->fetchall_arrayref;
>
>                 if (defined $result) {
>my @record = case_check($result, @keyparts);
>                         if(@record) {
>                                 @record = @{$record[0]}; #adjust for fetchall
>                                 my $Record = $self->{record}->new();
>                                 my @realFieldnames = 
> $self->{record}->FIELDS();
>                                 foreach (@realFieldnames) {
>                                         my $value = shift @record;
>                                         $value = "" unless defined 
> $value; # promote undef to ""
>                                         $Record->$_($value);
>                                 }
>                                 push @records, $Record;
>                         } else {
>                                 push @records, undef;
>                         }
>                 } else {
>                         push @records, undef;
>                 }
>         }
>         $self->{driver}->disconnect();
>
>         return @records;
>}
>
># getAll($userID, $setID)
>#
># Returns all problems in a given set. Only supported for the problem and
># problem_user tables.
>
>sub getAll {
>         my ($self, @keyparts) = @_;
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>
>         croak "getAll: only supported for the problem_user table"
>                 unless $table eq "problem" or $table eq "problem_user";
>
>         my @keynames = $self->sqlKeynames();
>         pop @keynames; # get rid of problem_id
>
>         my ($where, @where_args) = $self->makeWhereClause(0, @keyparts);
>
>         my $stmt = "SELECT * FROM `$sqlTable` $where";
>         $self->debug("SQL-getAll: $stmt\n");
>
>         my @records;
>
>         $self->{driver}->connect("ro");
>
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         $sth->execute(@where_args);
>         my $results = $sth->fetchall_arrayref;
>my @arr_res = case_check($results, @keyparts);
>
>         foreach my $result (@arr_res) {
>                 if (defined $result) {
>                         my @record = @$result;
>                         my $Record = $self->{record}->new();
>                         my @realFieldnames = $self->{record}->FIELDS();
>                         foreach (@realFieldnames) {
>                                 my $value = shift @record;
>                                 $value = "" unless defined $value; # 
> promote undef to ""
>                                 $Record->$_($value);
>                         }
>                         push @records, $Record;
>                 }
>         }
>         $self->{driver}->disconnect();
>
>         return @records;
>}
>
>sub put($$) {
>         my ($self, $Record) = @_;
>
>         my @realKeynames = $self->{record}->KEYFIELDS();
>         my @keyparts = map { $Record->$_() } @realKeynames;
>         croak "(" . join(", ", @keyparts) . "): not found (use add)"
>                 unless $self->exists(@keyparts);
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @fieldnames = $self->sqlFieldnames();
>         my $fieldnames = join(", ", @fieldnames);
>         my $marks = join(", ", map { "?" } @fieldnames);
>
>         my @realFieldnames = $self->{record}->FIELDS();
>         my @fieldvalues = map { $Record->$_() } @realFieldnames;
>
>         my ($where, @where_args) = $self->makeWhereClause(1, map { 
> $Record->$_() } @realKeynames);
>
>         my $stmt = "UPDATE `$sqlTable` SET";
>         while (@fieldnames) {
>                 $stmt .= " " . (shift @fieldnames) . "=?";
>                 $stmt .= "," if @fieldnames;
>         }
>         $stmt .= " $where";
>         $self->debug("SQL-put: $stmt\n");
>
>         $self->{driver}->connect("rw");
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         my $result = $sth->execute(@fieldvalues, @where_args);
>         $self->{driver}->disconnect();
>
>         unless (defined $result) {
>                 croak "(" . join(", ", @keyparts) . "): failed to UPDATE: 
> $DBI::errstr";
>         }
>
>         return 1;
>}
>
>sub delete($@) {
>         my ($self, @keyparts) = @_;
>
>         return 0 unless $self->exists(@keyparts);
>
>         my $table = $self->{table};
>         my $sqlTable = $self->{sqlTable};
>         my @keynames = $self->sqlKeynames();
>
>         croak "wrong number of keyparts for table $table (needs: @keynames)"
>                 unless @keyparts == @keynames;
>
>         my ($where, @where_args) = $self->makeWhereClause(1, @keyparts);
>
>         my $stmt = "DELETE FROM `$sqlTable` $where";
>         $self->debug("SQL-delete: $stmt\n");
>
>         $self->{driver}->connect("rw");
>
>         my $sth = $self->{driver}->dbi()->prepare($stmt);
>         my $result = $sth->execute(@where_args);
>
>         $self->{driver}->disconnect();
>         croak "failed to DELETE: $DBI::errstr" unless defined $result;
>
>         return $result;
>}
>
>################################################################################
># utility functions
>################################################################################
>
>sub makeWhereClause($@) {
>         my ($self, $use_binary, @keyparts) = @_;
>         my $binary_string = "";
>         $binary_string = "BINARY" if($use_binary);
>         my @keynames = $self->sqlKeynames();
>
>         my $where = "";
>         my @used_keyparts;
>
>         my $first = 1;
>         while (@keyparts) {
>                 my $name = shift @keynames;
>                 my $part = shift @keyparts;
>
>                 next unless defined $part;
>
>                 $where .= " AND" unless $first;
>                 $where .= " $binary_string $name=?";
>                 push @used_keyparts, $part;
>
>                 $first = 0;
>         }
>
>         my $clause = $where ? "WHERE$where" : "";
>
>         return ($clause, @used_keyparts);
>}
>
>sub sqlKeynames($) {
>         my ($self) = @_;
>         my @keynames = $self->{record}->KEYFIELDS();
>         return map { "`$_`" } map { 
> $self->{params}->{fieldOverride}->{$_} || $_ } @keynames;
>}
>
>sub sqlFieldnames($) {
>         my ($self) = @_;
>         my @keynames = $self->{record}->FIELDS();
>         return map { "`$_`" } map { 
> $self->{params}->{fieldOverride}->{$_} || $_ } @keynames;
>}
>
>sub debug($@) {
>         my ($self, @string) = @_;
>
>         if ($self->{params}->{debug}) {
>                 warn @string;
>         }
>}
>
>
># Temporary debugging function
>
>sub see_check {
>   my $what = shift;
>   my $msg = shift;
>   local *LOG;
>   open(LOG, ">>", "/tmp/check-log");
>   print LOG "$msg\n";
>   if($what == 0) {
>     close(LOG);
>     return();
>   }
>   my $result = shift;
>   my @keyparts = @_;
>   my ($ind, @arr_res, $jj);
>   @arr_res = @$result;
>   print LOG "\n";
>   @arr_res = @$result;
>   for $ind (0..(scalar(@keyparts)-1)) {
>     print LOG "$keyparts[$ind] <-> ";
>     for $jj (@arr_res) {
>       print LOG " " . $jj->[$ind];
>     }
>     print LOG "\n";
>   }
>  close(LOG);
>}
>
>sub case_check {
>         my $result = shift;
>         my @keyparts = @_;
>#       see_check(1, "In case check:", $result, @keyparts);
>         my ($ind, @arr_res);
>         @arr_res = @$result;
>         for $ind (0..(scalar(@keyparts)-1)) {
>                 if(defined($keyparts[$ind])) {
>                         @arr_res = grep {$keyparts[$ind] eq $_->[$ind]} 
> @arr_res;
>                 }
>         }
>#       see_check(1, "Leaving case check:", [@arr_res], @keyparts);
>         return(@arr_res);
>}
>
>1;

Prof. Arnold K. Pizer
Dept. of Mathematics
University of Rochester
Rochester, NY 14627
(585) 275-7767