Re^2: Why a reference, not a hash/array?

That makes sense, except that I now realise that somewhere in my mental model I don't understand where the data is that we're referencing.

When I do a "select * from foo where bar" using DBI, I first do something like $sth->execute() and then I do my while $sth->fetchrow_hashref(){}.

I guess I've never understood what those two steps mean. The first one gets all the matching data from the database into some temporary/intermediate place, and then the row code parses through it one row at a time? So, if I never need to use that pesky column with the 50KB of data in it, it never makes it into memory? So, where is it?

Nobody says perl looks like line-noise any more
kids today don't know what line-noise IS ...

Comment on Re^2: Why a reference, not a hash/array? Select or Download Code

Replies are listed 'Best First'.
Re^3: Why a reference, not a hash/array? by Hercynium (Hermit) on Jul 07, 2007 at 04:19 UTC
My understanding on this isn't the most solid, but I believe it depends on the DB itself and the optimization of the DBD driver. So, for example, you call execute() and DBI talks to the DB via an API to fetch the data into memory - the DB software's memory. When your perl app needs that data, you would call something like fetchrow_hashref(), which then makes the API call to retrieve each successive row's data from the DB to perl. So, the short answer is: When you use DBI like the docs recommend, the data is where you need it when you want it, without cluttering up the heap. (keeping your RAM usage lower)	[reply]
Re^3: Why a reference, not a hash/array? by doom (Deacon) on Jul 07, 2007 at 16:54 UTC
Essentially, you're worried about details you don't need to worry about. At a guess, when you do an "execute", the information isn't loaded into perl's memory yet, but rather will (usually) be sitting in a buffer inside the database itself. Once you do a "fetchall" of some sort, then the entire result set is definitely loaded into perl's memory, and for efficiency's sake what you want is to get a reference to that datastructure, you don't want to make a second copy of it just to save you from thinking about references. You understand, that when you do a: `$working_href = $returned_href;` [download] that's just making a copy of a scalar value, but if you do a: `%working = %returned;` [download] that makes a copy of the entire data-structure. That doubles you memory usage, and wastes time in making the copy... so you don't want to do that unless you have a really good reason (like you're planning on modifying the copy and you need to preserve the original). By the way, if you need to join an array given a reference to it, you just do this: `my $string = join " ", @{ $aref };` [download] There's no "extra step" you need to do.	[reply] [d/l] [select]
Re^3: Why a reference, not a hash/array? by FloydATC (Deacon) on Jul 08, 2007 at 10:46 UTC
The prepare/execute/fetch model is there because you will often want to prepare a statement once using placeholders, and then execute it several times using different data each time. (This is faster, and arguably more secure since it helps separate code from data) `my $sth = $dbh->prepare("insert into t (foo,bar) values (?,?)"); $sth->execute("foo", 1); $sth->execute("bar", 2); $sth->execute("baz", 3);` [download] Also, I tend to use the following loop a lot when fetching return data; it allows me to peek at the data and make certain changes that wouldn't have been efficient or possible on the DB server: `while (my $record = $sth->fetchrow_hashref) { # Manipulate record as needed $record->{'foo'} = &bar( $record->{'baz'} ); push @records, $record; }` [download] And voilá, I have an array of hashrefs. Very convenient.	[reply] [d/l] [select]