Post reply

Dealing with time ranges and representations - How much should I aggregate?

  • January 19, 2009 at 6:46 am

    #383753

    Hi,

    I'm not sure if there is a 'correct' answer for this question but I would like to see the various view points anyway.

    I have a performance monitoring application that collects various performance indicator statistics on many servers. Each and every hour several servers report their performance counter values so I'm getting many data points over many hours.

    The data is being provided for graphical representation and Im using 3rd party chart controls thats got some functions for playing with the presentation. The chief function is one that can aggregate many hours into a single day or even week or month in a range on a graph. This means depsite the fact my data is stored in hours I **can** still represent the data in a 'days' or 'weeks' or 'months' date grouping by invoking said chart control. This however, does nothing for the underlying performance of the query to obtain this data, which, by default, still provides a result for each hour, by each indicator, by each server, for each hour starting at the start of the query to the end of the query and depending on the search parameters.

    I believe I have two options. I can build a vb.net app that consumes the sql data and all the chart control to aggregate the ranges as its built to do. I have looped through a collection of time ranges that represnt a month range for example:

    dataPoint0 = select avg(indicatorvalue) from myTAble where (indicatorid = 3 and nodeid = 34) and (indate between queryMonth0.StartDate and QueryMonth0.Enddate)

    dataPoint1 = select avg(indicatorvalue) from myTAble where (indicatorid = 3 and nodeid = 34) and (indate between queryMonth1.StartDate and QueryMonth1.Enddate)

    Series.add(datapoint0)

    series.add(datapoint1)

    series.dategrouping = months

    or

    I can do all the math once and store caculated values of those ranges in what I call a static table (rather than processing it each time in an app).

    I'm leaning towards a datamodel where the data is already processed for the consumption of the application. ie. the %PRocessor time for ServerA on Jan 21 at 3:00pm will never change. But to set up and maintain the sp's requried to keep the data in the source and aggregated tables would be a bit complex vs. having some vb code that runs the query each time. We are talking about 30 users max ever clicking and looking at graphs.

    I've probably made myself sound insane with this....but welcome to my world of novice datamodeling planning. 🙂

  • January 19, 2009 at 7:31 pm

    #929453

    Perhaps pre-aggregated and dynamic crosstabs would help a bit...

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/cross+tab/65048/

    I've had a lot of success with the combination.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 19, 2009 at 10:41 pm

    #929494

    Work smart not hard. Spend more time thinking about the problem and less time coding:)

    Feed your data RAC:

    www.rac4sql.net

    Feed your head:

    www.beyondsql.blogspot.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply