On 1/5/07 10:53 PM, Neal Clark wrote:
> my $sql = "SELECT * FROM malware_logs";
> $Rose::DB::Object::Manager::Debug = 1;
> my $iterator = MalwareLog::Manager->get_objects_iterator_from_sql($sql);
Just an aside: in the case of this simple query, there's no reason to use a
*_from_sql() Manager method. You could just do this:
$iterator = MalwareLog::Manager->get_blah_iterator();
where "blah" is whatever you used for the base name in your call to
make_manager_methods():
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Manager.pm#mak
e_manager_methods
On 1/5/07 10:53 PM, Neal Clark wrote:
> On Jan 5, 2007, at 5:26 PM, Perrin Harkins wrote:
>> The DBI drivers for most databases (and this includes Postgres and Oracle)
>> will load the entire result set into your local machine's RAM even before you
>> fetch the first row. With MySQL and Oracle, you can tell them not to do
>> this. With Postgres, I think you have to use cursors.
>>
>> For MySQL, just do this before issuing your large query:
>> $dbh->{'mysql_use_result'}=1;
>>
>> You probably want to set it back afterward, since the default is better for
>> small queries (it frees up resources on the server faster).
>
> Yes, as far as I can tell that is exactly what's happening! Where can
> I set this in the $dbh object managed by Rose::DB?
The simplest way is to change your Rose::DB registry entry to use a DSN
instead of separate database/host/port/etc. attributes, then include the
mysql_use_result option as described in the DBD::mysql docs. Example:
Existing data source registry entry:
My::DB->register_db(
domain => 'production',
type => 'main',
driver => 'mysql',
database => 'mydb',
host => 'dbsrv',
username => 'joeuser',
password => 'mysecret');
New, equivalent version with mysql_use_result turned on:
My::DB->register_db(
domain => 'production',
type => 'main',
driver => 'mysql',
dsn => 'dbi:mysql:database=mydb;host=dbsrv;mysql_use_result=1',
username => 'joeuser',
password => 'mysecret');
I'm not sure if mysql_use_result works as a connect option. If so, this
would work too:
My::DB->register_db(
domain => 'production',
type => 'main',
driver => 'mysql',
database => 'mydb',
host => 'dbsrv',
username => 'joeuser',
password => 'mysecret',
connect_options => { mysql_use_result => 1 });
Finally, if you only want to turn this on for particular calls, remember
that the db is an optional argument to all Manager methods. You can always
make/get a $db, yank out the $dbh, set the attribute manually, and pass the
$db as a parameter to your Manager calls.
$db = My::DB->new(...);
$db->dbh->{'mysql_use_result'} = 1;
$iterator = MalwareLog::Manager->get_blah_iterator(db => db, ...);
> Thanks again everyone! I'll set those loader.conf variables, try re-
> loading the db as InnoDB and then let y'all know what happens. But
> really, is the call I'm making to get_object_iterators_from_sql the
> right way to go about doing what I'm doing?
Earlier it seemed like you were just trying to get the last two rows from
the table. Now in this post you're trying to iterate over all rows. Are
these real tasks in your code, or just examples you're trying in order to
debug a problem? Either way, both should be possible (the latter with the
help of the mysql_use_result option).
-John
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/rose-db-object
