Re: Newbie question... Fetch performance... - Mark Lybrand - com.mysql.lists.perl

7 messages in com.mysql.lists.perlRe: Newbie question... Fetch performa...

From	Sent On	Attachments
David Jacobs	23 Mar 2000 15:53
Jochen Wiedmann	02 Apr 2000 16:05
Jon and Jenny Steltenpohl	02 Apr 2000 20:04
indrek siitan	02 Apr 2000 22:23
Mark Lybrand	02 Apr 2000 22:43
Jon and Jenny Steltenpohl	03 Apr 2000 00:09
indrek siitan	03 Apr 2000 09:43

Subject:	Re: Newbie question... Fetch performance...
From:	Mark Lybrand (mark...@home.com)
Date:	04/02/2000 10:43:54 PM
List:	com.mysql.lists.perl

And, Jon, if you are worried about sucking a large amount of data into memory, then you could try the following:

1. Prepare your SQL statement 2. Get your record set 3. Generate your random numbers (or determine what section of the record set you want) 4. Cycle through the record set, popping off one at a time (counting as you go) 5. When your count meets the condition (equal to a random number, within a segment of the record set you want, etc), do whatever you want with the record

This algorithm assumes that you don't care what happens to the records that don't meet the condition.

Mark :)

Hi,

Okay, the way I read it with DBI, you're supposed to do all of your selection, sorting, and "kitchen sink" criteria in your prepared SQL statement. Then, "fetch" merely gives you a row by row method of returning that data? But, what if you want to move and maneuver in a returned record set, is there any way to do this without a new query or dumping it all into a hash?

sorry for giving such a short answer to this long question, but I'm dead sleepy and a short answer is still better than no answer. :)

yes. you've right. executing a query consists of 4 steps:

(1) prepare the query with $sth=$dbh->prepare("SELECT * FROM table");

(2) execute the query with $sth->execute;

(3) fetch results.

there are a couple of more, but generally you would use one of these 2 fetches:

(a) the row-by-row you described:

while ($dtrow=$sth->fetchrow_arrayref) { print "field1: $dtrow->[0]\n"; print "field2: $dtrow->[1]\n"; print "field3: $dtrow->[2]\n"; }

(b) fetch all the results at a time, so you can maneuver around:

$num_of_rows=$sth->rows; $dtrows=$sth->fetchall_arrayref;

$i=0; while ($i<$num_of_rows) { print "field1: $dtrows->[$i][0]\n"; print "field2: $dtrows->[$i][1]\n"; print "field3: $dtrows->[$i][2]\n"; $i++; }

or whatever you want to do with the data.

(4) dispose the query: $sth->finish;

Rgds, Tfr

-----Original Message----- From: Jon and Jenny Steltenpohl [mailto:jonc...@earthlink.net] Sent: Sunday, April 02, 2000 10:05 PM To: msql...@lists.mysql.com Subject: Newbie question... Fetch performance...

Hi All,

It's Jon the newbie here. I'm trying to teach myself all of the stuff I need to set up a basic weblog with Perl and MySQL, and in my self education, I'm starting to question my concept of how DBI::MySQL works.

Basically, I'm not sure how to use the DBI methods to retrieve records out of a selected query dataset. I'm sitting here with Perl in a Nutshell's database chapter, section 20.5 of the MySQL manual ("MySQL Perl API"), and Jochen's summary at the Perl/DBI site in front of me. And, I've got fetch, fetchall_arrayref, and the other fetch functions taunting me with chants of "newbie, newbie, newbie".

I understand how to prepare an SQL query and I understand that once you've prepared the statement, you can go ahead and execute it. (Jochen, your list of supported functions answers a lot of my questions on that end. Very well written, thank you.)

But, I think I'm projecting Perl like hash functionality onto the fetch functions (because I read the chapters about DBM hashes before I read the DBI sections). I just want to make sure I'm understanding properly.

For instance, say I have a basic two table relational structure where StoryID is the key...

Story (StoryID, StoryBlob, StoryAuthorID) Comment (CommentID, StoryID, CommentBlob, CommentAuthorID)

And, say I want to pull up all of the Comments matching a certain StoryID. Simple enough SQL statement. Okay, now, if I want to display the first 10 results (or 21 to 30), I see that MySQL supports a "limit" statement.

But, say ,theoretically, I want to pull 3 random rows from that query? Is there any way for me to use the rows in any order I want? I mean, I can grok that I could put all 10 rows into a hash and then use perl to manipulate it, but is there any way to tell perl to pick row 5, 2 and 8 (in that order) from an executed query of 10 rows? Or does fetch simply offer me a row by row method of going through the dataset where each time I get a new row, fetch is primed for row+1?

In other words, are there ways to execute new queries criteria on top of your current query or to use Perl like hash manipulations? Or do you simply need to prepare a new statement and execute the new statement that includes the current one? If I want to pull a specific Comment out by CommentID, would I just go ahead and do a new query, or is there some way to isolate just that one. Or, say I wanted to select only certain Comment Author ID's from my current query... again, can I use Perl hash functions, or is the proper method to just do a new query? I assume I can dump all of a query into a hash, but that this would have serious memory/performance implications if I get into the hundreds of records.

Also, if I am just using a fetch method and I get to the end of the query, does the next fetch command cause an error or does it restart back to the first row returned? Do you just execute a new query at that point?

Sorry if some of these questions are non-sensical or obvious. I am a newbie, so please be gentle. I have a sneaking suspicion that they are, but like I said, the DBM docs show hash's, but the DBI docs just show "fetchs", and I want to make sure I'm not missing something.

Thanks much,

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail msql...@lists.mysql.com instead.