Memory limit with large number of records

bunner_bob

I'm working on a time accounting system for a graphic design firm. One of the modules, called "Analyze", lets the user retrieve a bunch of time and expense entries based on parameters they select.

They can choose a client (or all clients), project (or all projects), a date range, etc.

The module defaults to "everything" (all clients, all projects, unspecified date range) and under these conditions is currently trying to retrieve about 13,000 records with the first query and 6,000 with the second. This generates a memory limit error on my client's host (Interland). I've bumped the limit up via ini_set(), but I wonder if there's a better way to go about it.

Under this default condition, the script runs two queries:

"SELECT project_time.time_id, project_time.project_id, project_time.date, project_time.employee_id, project_time.activity_id, project_time.hours, project_time.rate, project_time.notes, project_time.invoice_id FROM project_time ORDER BY date";

and

"SELECT project_expenses.expense_id, project_expenses.project_id, project_expenses.date, project_expenses.employee_id, project_expenses.item_id, project_expenses.quantity, project_expenses.price, project_expenses.notes, project_expenses.invoice_id FROM project_expenses ORDER BY date";

There's no "where" condition in this case, since no filters have been specified. I do specify the table in each field ("table.field") because when I AM specifying conditions, in some cases it involves a table join.

Now, it may seem unrealistic to retrieve this many results (and it does result in a long html page) but my system also formats the results into a tab-delimited text file (parked temporarily in a session variable) which can be downloaded by clicking a link and then opened in Excel - so though working with this much data in a browser doesn't make much sense, there is a way to post-process it more practically.

Is it expected that I'd run into this memory limit issue given the number of records being retrieved? Is the only practical way to get around it (and still get all the records) to bump up the memory? Should I somehow "segment" my query - do multiple queries with LIMIT and then assemble them (in which case, if I'm doing the assembly prior to delivering to the browser, am I likely to run into the same problem)?

Any guidance will be most appreciated!

bastien

Don't give them the option to run it without filters as the problem is only going to get worse...make the user input some criteria and then force a tab|csv file to download so the user can use a desktop app like excel to do further work on it.

Another (more expensive) option is to get another server just for reporting and replicate the data, doing all reporting from this second server

bunner_bob

I was thinking of at least defaulting it to a date range (with the memory allocation boost to 64 MB I can show about 5 months worth of time and expense entries, so I'd probaly set it to a calendar quarter). Another option would be to default it to a single project.

Currently the Analyze module is only usable by someone logged in as admin - which is one guy at this company. It's easy enough to tell him "hey, if it hurts when you do that, don't do that". It doesn't break anything, just gives him a fun message, so no harm.

But of course, it would be nice if everything was perfect...

Speaking of perfect, you would be appalled at the lack of perfection among the time tracking systems out there. Either kludgey Filemaker systems or just clunky and broken. My client's office is all Mac. We tried out a system called Billquick, which runs on a PC but offers non-PC users a web time/expense entry module that would make any decent programmer cringe. I spent hours feeding the company bug reports and telling them how to fix their product. Finally we all just gave up - said "I can build a better app than any of these" - and (humbly) I think I have.

Muchas gracias to all the kind folks who continue to take the time to help those of us who need it. I couldn't make it without y'all.

Sxooter

Are you then, after getting this data, doing something to it before showing it to the user, like aggregating it or something?

If so, then try to do as much of that as you can in the database.

Also, look at using a cursor and fetching the result in chunks. If you're using a database that supports cursors.

bunner_bob

Originally posted by Sxooter
Are you then, after getting this data, doing something to it before showing it to the user, like aggregating it or something?

If so, then try to do as much of that as you can in the database.

Yes - summing up various things and presenting subtotals and totals. But also, showing all the line items that sum up to that total (they need to be able to review all the entries).

For instance, when I'm retrieving Project Expenses, each row contains a quantity and price. As I go through the results, I add the net (quantity * price) to a running subtotal for this item (CDRs, color laserprints, etc.) as well as a running subtotal for all items.

Likewise, for Project Time, I multiply each row's hours by the rate, running subtotals for each activity type (creative fees, production, etc.) as well as running subtotal for all time.

In addition to displaying the individual rows/entries of course.

Can you elaborate a little on what you mean by doing it in the database? There are a lot of possible combinations (retrieve everything for a particular client (i.e. all their projects), for a particular project, only uninvoiced entries for a client/project, entries from a particular date range, and combinations of these. So pre-calculating and storing the results might be unrealistic.

Are you thinking in terms of initiating some of the math in my query?

Also, I have thought it'd be possible to write the item net (quantity price or hours rate) into another db column, if that'd speed things. But I still want to retrieve the quantity and price and display them. It'd save doing the math in the retrieval anyway.

Also, look at using a cursor and fetching the result in chunks. If you're using a database that supports cursors. [/B]

I know I can do a LIMIT, and run multiple queries, aggregating the results before displaying them. Is this what you mean by cursors?

The db is MySQL, btw.

Sxooter

Are you thinking in terms of initiating some of the math in my query?

That's exactly what I'm suggesting. It's almost always faster to let the database do the math than to do it by hand later on, since it's being done closer to the origin of the data, there's less of it to move through the smaller, slower pipe of a network connection.

Pre-filled columns are also helpful for speeding things up, but if the math is simple, it's usually no real gain. For complex things however, the savings can be noticeable.

bunner_bob

Originally posted by Sxooter
That's exactly what I'm suggesting. It's almost always faster to let the database do the math than to do it by hand later on, since it's being done closer to the origin of the data, there's less of it to move through the smaller, slower pipe of a network connection.

Pre-filled columns are also helpful for speeding things up, but if the math is simple, it's usually no real gain. For complex things however, the savings can be noticeable.

Something like:

SELECT project_id, quantity, price, quantity * price AS net,...

Is that the proper syntax, or is it trickier than that?

Sxooter

That's the basic. With sub queries, joins and unions, you can basically do all your report math in the database. I know that older versions of MySQL don't do all of that, but often using a temporary table can do the same thing.