| Subject: | "where" key should be sorted, to allow for better performance |
Hello,
This is a follow up on and old conversation about performance when compared to DBIx::Abstract module. The issue came up at the time that perhaps the hash key ordering wasn't reliable.
After consulting with meryln and tye at PerlMonks, I have a solution. The answer is to sort the keys in the hash so they are in a reliable order. This should be backwards compatible because the keys were already being returned in an apparently random order anyway.
This allows you to use SQL::Abstract to prepare a statement once, and then call it repeatedly with just the bind variables. Future hashes passed in with the same keys should sort the same way, and thus return bind variables in the same order.
A seperate which list request would be to have a way to explictly order the bits of the SQL statement, for cases when the order could make a difference with the SQL performance.
Thanks again for the useful module! Here's my patch to sort the keys. It needs some more testing:
--- /usr/local/lib/perl5/site_perl/5.8.0/SQL/Abstract.pm Fri Sep 27 13:06:32 2002
+++ Abstract.pm Tue May 6 10:19:48 2003
@@ -350,7 +350,8 @@
return wantarray ? ($wsql, @sqlv) : $wsql;
}
elsif (ref $where eq 'HASH') {
- while (my($k,$v) = each %$where) {
+ for my $k (sort keys %$where) {
+ my $v = $where->{$k};
if (! defined($v)) {
# undef = null
push @sqlf, "$k $SQL{null}";