The first is that if you introduce a little latency between your database server and your client machine (eg a firewall between the two, some load-balancing software, etc), the effect of that can rapidly dwarf the overhead of hashes. (True story. A database loading script for Sybase I was working on slowed massively. The problem? The rows got too big for the packets, and passing data slowed massively.)

Do you know where your bottlenecks are? Immediately leaping to use arrays rather than hashes is an excellent example of premature optimization. Depending on your queries, the simple act of organizing your tables well and adding the right indexes can make a far larger difference than the act of accessing data. If it is a user visible request, how many hash accesses are you really going to do? How much string manipulation are you going to do right afterwards that is just as inefficient?

Which all comes back to the fundamental point that any kind of optimization doesn't matter unless you need it. It sounds silly when said that way, but it is true. Either you need to hit a performance mark, in which case you are really, really sad if you don't, or you don't have a problem in which case it didn't matter. There isn't much ground between that.

So yes. Arrays are faster than hashes. Wonderful. If I was going to work on a data warehouse, I might care. But I don't. So as long as I can get my measly 80,000 row tables calculated and loaded from scratch in a night, I don't care about efficiency. Hmm, I am nowhere close to having a problem now, how about later? Well in 5 years they will be 160,000 row tables, and hardware will be 10x as fast. I am failing to see a problem here.

Now if you are pushing the envelope, then disregard this. There are people who work with data warehouses and need to really understand performance. (Though competent ones seem to make hard decisions about what is penny wise and pound foolish pretty ruthlessly. A friend of mine told me how he used closures pretty ruthlessly as an abstraction technique in a loading script. The overhead of calling all of those functions took an extra $30K of hardware, the project got delivered 6 weeks sooner, it was worth it.)

Likewise high performance websites (which I thankfully have little performance with - nor do I want to) have their own needs and may care. But the average corporate intranet site gets how many hits per minute? Again, if it is an issue for you, then worry about it. It isn't for most of us though.

PS Please don't mistake my attitude for an implicit admission that I am unable to get performance when I want it. Ask jeffa about that. :-)

True story. A database loading script for Sybase I was working on slowed massively. The problem? The rows got too big for the packets, and passing data slowed massively.

I'm sure you know this - but you can configure the Sybase database server to handle larger packets (up to 8k, I think), and then the client apps can use this as well (I think DBD::Sybase can do this - if not I'll have to add it in!).
It's a potentially huge performance increase for certain types of applications...

Michael

Thanks, I did know that. The setting in DBD::Sybase is already there, it is called packetSize.

Doesn't help if your OS setting don't support it, or if you don't control the database...

I tend to either use hashes or bound values more than arrays anyway.

I use HTML::Template quite a bit and often use the hashref/arrayref calls to match up with a template. For example:

my @nodes = ();

my $sth = $dbh->prepare(q{
   SELECT node_id, title, body FROM node WHERE parent_id = ?
});

$sth->execute($parent_id);
while (my $row = $sth->fetchrow_hashref) {
    push @nodes, $row;
}
$sth->finish;

foreach my $node (@nodes) {

    $sth->execute($node->{node_id});
    $node->{children} = $sth->fetchall_arrayref({
                            node_id => 1, title => 1
                        });
    $sth->finish;
}

$t->param(NODES => \@nodes);
[download]

It's not designed for performance. If it isn't fast enough then there is loads of things I'd do before changing hashes to arrays...

gav^

You hit the nail on the head! gav^++

Having discussed this issue a bit further with trs80, i strongly feel that he should be using HTML::Template instead of jumping through hoops do suppress the display of one or more columns. {chuckle} And what data structure does HTML::Template want for its loops? A list of hashes. ;)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Yes, i mentioned that i tend to avoid using hashes to store database result sets, but that doesn't mean that i avoid hashes in database code. One of my favorite tricks is to store the names of columns in an array and a hash:

my $sth = $dbh->prepare('select stdout,stdin from stderr');
$sth->execute;

my @field = @{$sth->{'NAME'}};
my %field = map { $field[$_] => $_ } (0..$#field);
my $rows  = $sth->fetchall_arrayref();
[download]

Now i know what belongs where, but have other issues with your code, namely printing inside a sub like that i meant - storing the 'print' results in a string and returning that. How about transforming the data structure into another one and then having one printing subroutine? The obvious drawback is memory consuption, of course.

My other problem is sorting outside the database. I am sure there is at least one situation 'out there' that calls for it, but most of the time you should let the database do it.

But, good stuff none-the-less. I am not going to refute it. I'll leave that for princepawn, whom you didn't see coming. ;)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)