I decided to code up your benchmark myself, and I noticed something
else while doing it. You're fetching across two foreign objects in
this bit:
my $var = $cidade->estado_obj->pais_obj->nome;
To do that in one query using the RDBO manager, the require_objects
parameter should be:
require_objects => [ 'estado_obj.pais_obj' ]
instead of what I suggested earlier.
Anyway, when I created my own version of the benchmark I got some very
different results. I added 1,000 rows to each table, and made two
versions of the RDBO bench: one "fast" using the tweaks suggested
earlier, and one "slow" using only the persistent db connection. Here
are the results when running for a minimum of 10 CPU seconds (-10
argument to cmpthese()):
s/iter CDBI RDBOSlow RDBOFast
CDBI 3.09 -- -43% -90%
RDBOSlow 1.76 75% -- -82%
RDBOFast 0.322 858% 448% --
As you can see, even the "slow" one significantly faster than CDBI.
I'm running the latest versions of all the modules (CDBI 3.0.14, RDBO
0.65) and MySQL 5.0.18. I'm not sure why your results differ so much
from mine.
The code I ran is included below. Uncomment the Setup_DB() line to
create new database tables with new data, or leave it commented out to
use your existing tables with the same names. Try it and let me know
how it runs for you.
-John
---
use warnings;
use strict;
#Setup_DB();
sub Setup_DB
{
use DBI;
my $dbh = DBI->connect("dbi:mysql:database=test", "root");
# Drop existing tables, if any, ignoring errors:
#$dbh->{'RaiseError'} = 0;
#$dbh->{'PrintError'} = 0;
#$dbh->do('DROP TABLE cidade');
#$dbh->do('DROP TABLE estado');
#$dbh->do('DROP TABLE pais');
#$dbh->{'PrintError'} = 1;
#$dbh->{'RaiseError'} = 1;
$dbh->do('
CREATE TABLE cidade
(
id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(255),
estado INT
)
');
$dbh->do('
CREATE TABLE estado
(
id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(255),
pais INT
)
');
$dbh->do('
CREATE TABLE pais
(
id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(255)
)
');
# A real db would probably have these indexes
#$dbh->do('CREATE INDEX cidade_estado_idx ON cidade (estado)');
#$dbh->do('CREATE INDEX estado_pais_idx ON estado (pais)');
foreach my $i (1 .. 1000)
{
$dbh->do("INSERT INTO pais (id, nome) VALUES ($i, 'pais $i')");
$dbh->do("INSERT INTO estado (id, nome, pais) VALUES ($i, 'estado
$i', $i)");
$dbh->do("INSERT INTO cidade (id, nome, estado) VALUES ($i,
'cidade $i', $i)");
}
}
package CidadeDBI;
use base 'Class::DBI';
__PACKAGE__->connection("dbi:mysql:database=test", "root");
__PACKAGE__->table("cidade");
__PACKAGE__->columns(Essential => qw|id nome estado|);
__PACKAGE__->has_a(estado => 'EstadoDBI');
package EstadoDBI;
use base 'Class::DBI';
__PACKAGE__->connection("dbi:mysql:database=test", "root");
__PACKAGE__->table("estado");
__PACKAGE__->columns(Essential => qw|id nome pais|);
__PACKAGE__->has_a(pais => 'PaisDBI');
package PaisDBI;
use base 'Class::DBI';
__PACKAGE__->connection("dbi:mysql:database=test", "root");
__PACKAGE__->table("pais");
__PACKAGE__->columns(Essential => qw|id nome|);
package RDBSetup;
use base 'Rose::DB';
use Rose::DB::Registry;
# Create a private registry for this class
__PACKAGE__->use_private_registry;
# Register your lone data source using the default type and domain
__PACKAGE__->register_db
(
driver => 'mysql',
database => 'test',
username => 'root',
);
package CidadeRDB;
use base 'Rose::DB::Object';
__PACKAGE__->meta->table('cidade');
__PACKAGE__->meta->columns(qw(id nome estado));
__PACKAGE__->meta->primary_key_columns('id');
__PACKAGE__->meta->foreign_keys
(
estado_obj =>
{
class => 'EstadoRDB',
key_columns => { estado => 'id' },
}
);
__PACKAGE__->meta->initialize;
__PACKAGE__->meta->make_manager_class('cidade');
sub init_db { RDBSetup->new }
package EstadoRDB;
use base 'Rose::DB::Object';
__PACKAGE__->meta->table('estado');
__PACKAGE__->meta->columns(qw|id nome pais|);
__PACKAGE__->meta->primary_key_columns('id');
__PACKAGE__->meta->foreign_keys(
pais_obj => {
class => 'PaisRDB',
key_columns => { pais => 'id' },
}
);
__PACKAGE__->meta->initialize;
__PACKAGE__->meta->make_manager_class('estado');
sub init_db { RDBSetup->new }
package PaisRDB;
use base 'Rose::DB::Object';
__PACKAGE__->meta->table('pais');
__PACKAGE__->meta->columns(qw|id nome|);
__PACKAGE__->meta->primary_key_columns('id');
__PACKAGE__->meta->initialize;
__PACKAGE__->meta->make_manager_class('pais');
sub init_db { RDBSetup->new }
package main;
use Benchmark 'cmpthese';
cmpthese(-10,
{
CDBI => \&CDBI,
RDBOFast => \&RDBOFast,
RDBOSlow => \&RDBOSlow,
});
my $db = RDBSetup->new;
sub RDBOFast
{
my $objs =
CidadeRDB::Manager->get_cidade(
db => $db,
query_is_sql => 1,
prepare_cached => 1,
require_objects => [ 'estado_obj.pais_obj' ],
query => [ nome => { like => '%a%' } ]);
foreach my $cidade (@$objs)
{
my $x = $cidade->id;
my $y = $cidade->nome;
my $z = $cidade->estado_obj;
my $var = $cidade->estado_obj->pais_obj->nome;
}
}
sub RDBOSlow
{
my $objs =
CidadeRDB::Manager->get_cidade(
db => $db,
#query_is_sql => 1,
#prepare_cached => 1,
#require_objects => [ 'estado_obj.pais_obj' ],
query => [ nome => { like => '%a%' } ]);
foreach my $cidade (@$objs)
{
my $x = $cidade->id;
my $y = $cidade->nome;
my $z = $cidade->estado_obj;
my $var = $cidade->estado_obj->pais_obj->nome;
}
}
sub CDBI
{
foreach my $cidade (CidadeDBI->search_like({ nome => '%a%' }))
{
my $x = $cidade->id;
my $y = $cidade->nome;
my $z = $cidade->estado;
my $var = $cidade->estado->pais->nome;
}
}
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
