We have a table with spatial data and some attributes and need to aggregate it so it could be used in various queries. We have the mechanism to do the aggregation on-the-fly via CLR stored procedures but I was wondering what would be the best way of caching this aggregated data in SQL Server 2008 R2 as the aggregation is very slow.
So we have table like this:
[TableId] [int] NOT NULL, [FeatureId] [int] NOT NULL, [MeasureFrom] [float] NOT NULL, [MeasureTo] [float] NOT NULL, [Value] [smallint] NOT NULL, [Timestamp] [datetime2](7) NOT NULL
FeatureId, MeasureFrom and MeasureTo basically define an interval and Rating is the value of this interval. Note that FeatureId actually references a real lines (shapes) from a different table. We have about 250k rows in this table currently with overlapping intervals and various timestamps (historical records). Our aggregation process tries to find the latest Value across all intervals so it needs to cut and join intervals based on timestamp and keeps the related value.
As the on-the-fly querying is slow and takes a lot of resources, we thought we might need to create another database storing the results of this aggregation. The source table is constantly updated (but not too often, once in a couple of days).
What would be the best way of creating this database and keeping the aggregated values up-to-date? I can think of using triggers, or scheduled task. I have no experience with SSAS but would that be more suitable?
Just to note, we have a few similar tables storing slightly different values (some with more that one Value column) and hence the reason for a separate database rather than another table in the original database.
How quick would be the querying of this separate database from the original database using cross-database joins?
Edited: To demonstrate what our "aggregation" does, here is some sample data:
FeatureId | MeasureFrom | MeasureTo | Value | Timestamp 1 | 1 | 20 | 2 | 2015-01-01 1 | 5 | 15 | 3 | 2015-01-02 1 | 9 | 10 | 8 | 2015-01-03
And the result we are getting:
FeatureId | MeasureFrom | MeasureTo | Value | Timestamp 1 | 1 | 5 | 2 | 2015-01-01 1 | 5 | 9 | 3 | 2015-01-02 1 | 9 | 10 | 8 | 2015-01-03 1 | 10 | 15 | 3 | 2015-01-02 1 | 15 | 20 | 2 | 2015-01-01
As you can see, the result actually contains more rows than the original data. Basically we need the latest Value at any point within the area. The latest value is determined based on value in the Timestamp column. When there is a gap within intervals, this should propagate to the resulting dataset.
The code is actually written in .NET and it is quite complicated. The algorithm is as follows: Get all data for a feature of interest, order the records by timestamp descending, process all records in a loop. Take the interval (MeasureFrom, MeasureTo), intersect it with the result that you have, if you get some part that you don't have already, add it to the result. Continue with the next record. Data represents surveys done regularly so further you go in time, the more data will be thrown away as you will already have newer Value for the same Interval.
Surveys are coming in with random Timestamps so it is not always the latest one that comes in. Also same process is then repeated for other features as you might be interested in getting values for a couple of features at once. And this is the result people would query often.
Ideally we would like to keep the aggregation result as close to being real-time as possible.
To explain the required algorithm a bit more, see the link posted by @dnoeth (in the comment below) http://sqlmag.com/sql-server/packing-intervals-priorities . It solves similar problem with the difference of using priorities instead of Timestamps and the solution described uses features from SQL Server 2012.