ResultSet and aggregation

views:

answers:

ResultSet and aggregation

Ok, I admit my situation is special

There is a data system that supports SQL-92 and JDBC interface

However the SQL requets are pretty expensive, and in my application I need to retreive the same data multiple times and aggregate it ("group by") on different fields to show different dimensions of the same data.

For example on one screen I have three tables that show the same set or records but aggregated by City (1st grid), by Population (2nd grid), by number of babies (3rd grid)

This amounts to 3 SQL queries (which is very slow), UNLESS anyone of you can suggest any idea any library from apache commons or from google code, so that I can select all records into ResultSet and get 3 arrays of data group by different fields from this single ResultSet.

Am I'm missing some obvious and unexpected solution to this problem?

+1 A:

If your database supports a materialized view and indexed views, you can gain significant performance by pre-aggregation the values in the view and using an index to retrieve the values very quickly.

You basically create a materialized view that contains the aggregations you need and then index it. You can join to it or just query straight it against it.

Based on your data City (1st grid), by Population (2nd grid), by number of babies (3rd grid) I don't think the overhead will hurt INSERTS/UPDATES/DELETES that much.

KM 2010-04-01 14:06:12

so that means for each and every query I need to create view, query the view using same SQL with different "group by" and then delete the view? Data system is "read only", there are no inserts and updates, just selects.

2010-04-01 14:10:14

no, create a materialized view `CityView` that is `SELECT City,aggregation(xyz) as xyz FROM YourTable GROUP BY City`, you can then `SELECT * FROM CityView WHERE...` and the result will be much faster than before. do this for each. If this is not possible, you need to explain more about your situation, give the columns in the table, and how many different things you group by, what indexes you have, what database you are using.

KM 2010-04-01 14:16:09

hm, thanks for the tip. I need to learn more about views

2010-04-01 14:21:47

if this is not a real database, I would suggest that you create three arrays of arrays: City[city,aggregation], Population[population,aggregation], and Babies[babies,aggregation]. Then write a loop that processes each item in your virtual table. For each iteration push the proper values into the City,Population, and Babies arrays. Whatever you do you will need to hit every row in your table, but with this you will only hit each one time, but fulfill your three aggregations.

KM 2010-04-01 14:29:47

ansaurus

tags:

views:

answers:

ResultSet and aggregation

related questions