For a given point in time, an asset has an effective price. Some assets have a new price once a week, some once a day. The most recent price is the "effective price".
The temporal table that stores the described relationship looks like this:
CREATE TABLE dbo.AssetPrice
(
AssetId int NOT NULL -- FK to the table dbo.Asset
,EffectiveDate datetime NOT NULL
,Price decimal NOT NULL
CONSTRAINT PK_AssetPrice PRIMARY KEY CLUSTERED (AssetId,EffectiveDate,Price)
)
The data looks something like this:
AssetId EffectiveDate Price
------- ------------- -----
1 2012-01-11 1.21
1 2012-01-12 1.22
2 2012-01-11 3.55
2 2012-01-12 3.60
3 2012-01-04 5.15
3 2012-01-11 5.14
To query for the effective price of an AssetId
is simple, but it takes a non-trivial amount of time to calculate.
It is ideal to store the data physically, so that only data changes to dbo.AssetPrice
requires a recalculation of the effective price. I believe I cannot create an indexed view because the pertinent aggregate functions are not allowed in an indexed view.
How do I tune the table to retrieve the effective price (most recent price) very quickly?
Basically you can use two different approaches:
Alter your table schema to include an interval. In this case, you need to store start AND end date to determine your effective period. With that, you just need to use a simple BETWEEN to get your desired price. You could also to add a non-clustered index on start and end dates and AssetId for optimal performance. By default, you could to add an end date as '9999-12-31' and, every time an asset have a new price, you end current period and starts a new one. I (personally) prefer this option.
Stick with this schema and create an non-clustered index on EffectiveDate and AssetId. You'll need to build an subselect to get max date for assets with effective date less than your desired date, like this:
.