Shannon, Bryan wrote: > To make the template more meaningful, I'm using fetchrow_hashref() in DBI to > provide template variables that are easy for template writers to write; > > This works quite well, but for large queries, the program spends most of its > time in Template::Stash::XS::get() (about 36% percent of its time, according > to dprofpp)
This is unlikely to change much if you use arrays. The Stash access shows up on your profile because it does quite a bit of work: expanding the dot notation, testing what kind of variable or object it has, etc. > ... Also, the overhead of using a hash for each row of data gets > impeding with large rowcounts. If memory overhead is the problem, you can avoid fetching all the data at once by using an iterator that fetches one row at a time. (Technically DBI fetches multiple rows at once with most databases, but you can avoid having more than what it's configured to fetch in memory at one time.) I think there's an example in the DBI plugin that will show you how to do it. > So, what I'm thinking about doing is creating something that will allow me > to supply a "map" that will allow template writer's to still use [% > data.field_a %] [% data.fieldb %] etc, but will use the map to map field_a > into it's column-ordered position in an array; This way I can use > fetchrow_arrayref() instead of fetchrow_hashref() (for the sake of > memory/speed efficiency). IIRC, the reason fetchrow_hashref is slow has more to do with looking up the names of the columns, than with the fact that it's a hash. If you just use fetchrow_arrayref (or better yet, bind_cols) and then put those values into your own hashref, it may be faster. > This would sort of be a Stash equivalent of a > Perl pseudo-hash Part of the reason pseudo-hashes got dropped from Perl is that they weren't that much faster than hashes. Seriously, my benchmarks have shown only about 15% speed difference between arrays and hashes, and that was ages ago. I think you are much too worried about the speed of hash lookups. > I'm not letting templates use DBI themselves, the templates just provide the > presentation for the rows, and the actual program engine decides how to best > perform the query... It just provides the data as hashes right now. Good plan. I think you should stick with it that way, but maybe write your own iterator class around a statement handle to take care of the memory problem you're having. You can pass that iterator object to the template instead of a list of hashes. - Perrin
