Re^6: Feasability of quick generation of complex report from large db?

Very interesting. I'm liking this immensely, so I'd like to ask some questions despite knowing they won't make me look smart:

Your query returns the count for every possible cgroup x pclass combination every time. That's 70 x 12,000 = 840,000 count results returned. Then what - in Perl take just the combinations I'm intereted in. No - maybe I can add to your query - since a report uses just 5 industry_groups, I'd add:

SELECT CG.answer as cgroup, CP.answer as pclass, count(*) FROM CG, CP
WHERE CG.company = CP.company AND (cgroup='a' OR ..... cgroup='e') GRO
+UP BY cgroup, pclass;
[download]

Which will reduce results significantly.

But - a single query on 15-million rows that returns ~50,000 results, faster than 350 queries on pairs of little ~10k~1k-row tables.....sounds possible.

Comment on Re^6: Feasability of quick generation of complex report from large db? Download Code

Replies are listed 'Best First'.

Re^7: Feasability of quick generation of complex report from large db?
by Jenda (Abbot) on Feb 07, 2008 at 00:48 UTC

With indexes on the right columns, definitely. Tables Answer1, Answer2, Answer3, ... in a database are at least as much a red flag as variables $Answer1, $Answer2, $Answer3, ... in Perl (or any other even remotely sensible programming language). Just as those variables should be made an array and the number an index, those tables should be changed just to a single table and the number made a column.

If you ever find yourself having to maintain a project with tables like that (it doesn't have to be foo1, foo2, ... it can be users_clientname1, users_otherclient, ...) run! Either to kick some butt or to find a new employer. I once had to do a few things in such a project, the number of TABLES in the database was in tens of thousands, insane. And all the butts responsible for that mess were long gone :-[

Jenda
Support Denmark!
Defend the free world!

[reply]

Re^7: Feasability of quick generation of complex report from large db?
by pc88mxer (Vicar) on Feb 06, 2008 at 20:00 UTC

Another option is to pre-compute this big query just once and store it in a table. Your smaller queries would just be selects from this table, and that would make them run really fast (if interactive response time is an issue.)

[reply]