Recordset performance question

[Kee Hinckley <na...@somewhere.com>]

I'm doing a lot of calls to recordset on various tables in a 
database.  Since I was doing a rather complicated Setup each time, I 
put it in a central routine.  And then it occured to me that there 
might be a benefit in caching the results of the setup, so I added 
that.  The code looks something like this.

sub setupDB {
     my $this = shift;
     my (%args) = @_;
     my ($db, $set, $connect);

     #
     # just do the database initialization once and use SaveAs for
     # future reference
     #
     if (!$this->{_db}) {
         $connect = 'DBI:' . $this->{dbiString} . ':' . $this->{database};
         new DBIx::Database({
                 '!DataSource' => $connect,
                 '!DBIAttr' => {RaiseError => 1, AutoCommit => 1},
                 '!Username' => $this->{username},
                 '!Password' => $this->{password},
                 '!KeepOpen' => 1,
                 '!SaveAs' => 'Articles',
             }) || die $DBI::errstr;

         $this->{_db} = 'Articles';

         my $dateparse = ...
         my $timeparse = ...


         $this->{_setup} = {
                 '!DataSource' => $this->{_db},
                 '!LongNames' => 2,
                 '!Serial' => 'id',
                 '!Filter' =>
                     {
                         'dtm' => $dateparse,
                         'dtc' => $dateparse,
                         'verifydate' => $dateparse,
                         'validdate' => $dateparse,
                         'approveddate' => $dateparse,
                         'pubdate' => $dateparse,
                     },
         };
     }

     #
     # check and see if we've already done the setup
     #
     return $this->{"_$args{'!Cache'}"}
         if ($args{'!Cache'} && $this->{"_$args{'!Cache'}"});

     #
     # otherwise do it now, merging in the new items with the global ones
     #
     my %setup = %{$this->{_setup}};
     my ($key, $val);
     while (($key, $val) = each(%args)) {
         next if ($key eq '!Cache');
         $setup{$key} = $val;
     }

     $set = DBIx::Recordset->Setup(\%setup);
     $this->{"_$args{'!Cache'}"} = $set if ($args{'!Cache'});
     return $set;
}


A call to this looks might look like:


     $set = $this->setupDB(
         '!Cache'        => 'listArticles',
         '!Table'        => 'article',
     );
     $args->{'$order'} = 'dtm DESC' if (!$args->{'$order'});
     $$set->Search($args);


The idea is to cache the return value from $set, similar to the way 
that DBIx::Ddatabase can use !SaveAs to cache the database parsing. 
The question is, "Does it gain me anything?"  The only drawback I've 
run across so far is that the I have to explicitly call Flush if I 
want things written out right away.  Are there any others?
-- 

Kee Hinckley - Somewhere.Com, LLC
http://consulting.somewhere.com/
nazgul@somewhere.com

I'm not sure which upsets me more: that people are so unwilling to accept
responsibility for their own actions, or that they are so eager to regulate
everyone else's.

---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org

Re: Recordset performance question

[Gerald Richter <ri...@ecos.de>]

Hi,

>
> The idea is to cache the return value from $set, similar to the way
> that DBIx::Ddatabase can use !SaveAs to cache the database parsing.
> The question is, "Does it gain me anything?"  The only drawback I've
> run across so far is that the I have to explicitly call Flush if I
> want things written out right away.  Are there any others?

I can not think of any drawbacks right now. Instead of calling Flush it's a
better idea to call $set -> ReleaseRecords, that does the Flush and frees up
all memory of cached records.

I would move the conditional that checks the cache to the top of the
routine. If your recordset is already in the cache, you don't need to do
anything else, just return it. Your database object should be already
created and saved when the recordset object was created. In case you need
your database object, you can retrive it with

DBIx::Database -> Get ($saveasname) ;

What do you gain? You don't save any database access, because
DBIx::Recordset already caches a lot of things internaly (e.g. all metadata
about the db is only retrived once), but you save the setup time for the
object (creating the internal data structures). Depending on how much sets
you have, this may count.

Gerald


-------------------------------------------------------------
Gerald Richter    ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting

Post:       Tulpenstrasse 5         D-55276 Dienheim b. Mainz
E-Mail:     richter@ecos.de         Voice:    +49 6133 925131
WWW:        http://www.ecos.de      Fax:      +49 6133 925152
-------------------------------------------------------------


---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org