Datagrid aggregation in each cell

[dcmetro]

Hi,

I have a search page which will display the results in a datagrid. The
grid's nature is, except the first column, every cell in every row is
an aggregate value. The grid will always have the same number of rows
and columns no matter what the search criteria is. It's only the values
in the cells that change.

I'm rewriting the existing application; At present they have the entire
calculation is done in the stored procedure and the result of the
stored procedure is simply displayed to the user. This approach
timesout most of the time.

Here's a sample datagrid I'm trying to achieve:

CITY ERRORS FAILURES
Austin 10 25
Boston 23 2
Chicago 13 16
No City Mentioned 2 3
Total 48 46

Please note there may be different kind of Errors and different kind of
Failures. We aggregate all Errors and Failures display the total errors
and total failures respectively against each city.

In the present DB model, they have a separate table for the city, a
table for the event names -(Errors and/or Failures) and a transaction
table that links those two tables with the station code and event code.


My question is, how much and what can we do in the stored procedure,
and what can we do in ADO.NET for the above case?

If my question doesn't make sense, please let me know I'll try to
rephrase it.

Thank you.

 

[W.G. Ryan - MVP]

The DataTable object has a good bit of power with the Compute method. It
allows for pretty powerful aggregation and it can be used to aggregate
related tables. However client side aggregation probably isn't a good idea
if you're dealing with a large amount of records. As far as timing out, so
many things can be affecting that. My first suggestion would be to look at
the query and really start to think about the table structures, indices, the
execution plan etc. Two tables joined by an intermediate third table, in
and of itself, is not somthing that should be causing huge performance
problems. Using Keys and/or indexing should be able to address a good bit of
that and there are certainly cases where doing an operation like that
without an index is so slow it's unbearable but after tuning the query,
everything works fine. However one of the biggest factors here , whether
you do it client or server side and if you go server side, how to make it
work, is the number of records involved. If may be worth creating a job
that runs those aggregates and sticks them into a table and then you just
query the table. The job can run for x minutes/hours whatever at a time when
you don't need the data (assuing there is such a time) so when you hit the
table, you're just querying 10 records or so.

If you're in the really large number realm, and you have a window that you
can use to aggregate everything, this is probably a good approach. Another
idea might be to consider using Analysis Services and building a cube. If
this data is read-only, creating a cube might be the best way to slice the
data and give you impressive performance.

Anyway, how many records are we talking about? Are there a lot of columns
in each of the two tables that are linked together by the third? Are those
fields that the joins are done on keyed or indexed? Can you post the query
that's running slow and at least a description of the parent tables' fields
that are used in the query? Thanks!

Bill

 

[Cor Ligthert [MVP]]

dcMetro,

I would not use a datagrid or whatever grid in your case.
It is only about 18 cells which you can do with labels with different
backgrounds and borders.

With those your problem will probably be much easier.

Just my idea

Cor

 

[nycmetro]

Ryan, and Cor, Thank you for your responses.

Here's some more information. The number of cities is 40 at this time
and is likely to grow. I simplified my requirement for explaining it.
Actually, the Errors and Failures are divided further into groups -
like for instance Error Group 1, Error Group 2, Error Group 3 Failure
Group 1, Failure Group 2, etc. which means, more columns - about 20 or
so. There's an aggregate column at the end as well.

The input for this search page are a date range, type of result -
whether in % or actual count. As the date range gets wider, the more
chances of runtime error in the present implementation.

Here's how the tables look like (I simplified it once again for the
sake of clarity)

tblCitiesEvents
CityEventID int PK
CityEventDate Date
CityID - FK
fName
lName

tblCities
cityID - PK
cityCode

tblEvents
eventID - PK
eventDescription

The existing stored proc is the size of about 4-5 printed pages. Yes,
the events table is indexed; this obviously has the most number of
records.

Given this many number of columns and rows, having Labels may not be
practical. But please correct me if I'm wrong.

Thanks for any/all inputs.