Introduction
Much like a junkie stealing your TV, I did it for the speeeed.
Field prefetch
DBR automatically profiles the fields you use. It saves this information and uses it in subsequent queries.
Consider the following:
# get all the cars salesman #3 sold
my $cars = $dbrh->cars->where(salesman_id => 3);
while( my $car = $cars->next ){
print $car->VIN . "\n";
}
Given this example: The first time this executes, it will fetch only the primary key for the cars table. Now suppose salesman #3 is fairly prolific in his sales. Upon iterating over these car records, it's going to have to fetch the VIN field many many times. This is horribly inefficient, BUT, prior to doing so, it records your usage of this field so that next time it remembers to fetch car_id and VIN.
It remembers each field that is used by the cars table on a per scope basis. it knows that you are executing $dbrh->cars->where from a specific line number and file, and records the fields you use accordingly.
The purpose of this is to select exactly the fields you need. No more, and no less... every time (other than the first time)
This is a common pitfall of ORMs, insofar as they tend to either fetch every field in the table, or make tons of itty bitty selects, which renders horrible performance.
Relationship Prefetch
When calling a relationship accessor, DBR reads ahead and fetches the relationship records it expects you'll use.
Consider the following:
# get all the cars salesman #3 sold
my $cars = $dbrh->cars->where(salesman_id => 3);
while( my $car = $cars->next ){
print $car->VIN . "\n";
while(my $option = $car->options->next){ #Just works the way it should
print "\t" . $option->name . "\n";
}
}
Given this example, we are iterating over cars. One would assume that having a nested database call inside a while loop would result in many unnecessary selects being executed. However, DBR reads ahead. upon the first call to car->options, an SQL query is executed that fetches all option records for all cars remaining in the resultset's record cache (max 1000 records) These are then cached, and another query is not made again unless the resultset is larger than that.