So a couple of months ago I wrote about a problem I was having with a reporting application, and the solution I was considering. In short, the problem was how to paginate results that may return multiple rows for each “object” due to the use of an outer join. I wound up going with the solution where I retrieved the IDs for each object first and then went back and used that list of IDs in an IN expression to grab the actual data.
That worked fine, but now I find myself in another sticky situation. The solution is ideal when you’re working with paginated results. I retrieve the IDs, then I retrieve the data, then I iterate over the data and convert it into a data structure (this is PHP, so an array of associative arrays) so that I can iterate over it in the presentation layer. When we’re talking about pages of 50 or 100 objects, the solution is fast and even somewhat straightforward.
The story doesn’t end here, though. The application also produces downloadable reports in CSV format, some of which contain many thousands of records. Naturally, I was using the same code to fetch the data from the database and prepare it for the presentation layer, the only difference being that if I wasn’t paginating, I skipped the “retrieve IDs” step and just used the search expressions in the query that retrieved the actual data. Unfortunately, for big reports, copying the results from the database into a datastructure that lives in memory can result in massive memory usage. So now I have another hurdle to cross, how to restructure my code so that I can iterate over the results from the database and present them in one step rather than accumulating everything in memory and then spewing it out all at once. The trick, of course, is to accomplish this in such a way that I can still reuse my code easily and without totally mixing up the business logic (which turns the data in the database into presentable results) with the presentation code.
I know that I could just raise the memory_limit setting in php.ini, but that doesn’t strike me as a real fix.
Revisiting my data fetching puzzle
So a couple of months ago I wrote about a problem I was having with a reporting application, and the solution I was considering. In short, the problem was how to paginate results that may return multiple rows for each “object” due to the use of an outer join. I wound up going with the solution where I retrieved the IDs for each object first and then went back and used that list of IDs in an
INexpression to grab the actual data.That worked fine, but now I find myself in another sticky situation. The solution is ideal when you’re working with paginated results. I retrieve the IDs, then I retrieve the data, then I iterate over the data and convert it into a data structure (this is PHP, so an array of associative arrays) so that I can iterate over it in the presentation layer. When we’re talking about pages of 50 or 100 objects, the solution is fast and even somewhat straightforward.
The story doesn’t end here, though. The application also produces downloadable reports in CSV format, some of which contain many thousands of records. Naturally, I was using the same code to fetch the data from the database and prepare it for the presentation layer, the only difference being that if I wasn’t paginating, I skipped the “retrieve IDs” step and just used the search expressions in the query that retrieved the actual data. Unfortunately, for big reports, copying the results from the database into a datastructure that lives in memory can result in massive memory usage. So now I have another hurdle to cross, how to restructure my code so that I can iterate over the results from the database and present them in one step rather than accumulating everything in memory and then spewing it out all at once. The trick, of course, is to accomplish this in such a way that I can still reuse my code easily and without totally mixing up the business logic (which turns the data in the database into presentable results) with the presentation code.
I know that I could just raise the memory_limit setting in
php.ini, but that doesn’t strike me as a real fix.Commentary
Previous post
Karl Rove’s significanceNext post
Where are the women?