+ Reply to Thread
Results 1 to 2 of 2

Thread: tune performance of a SQL Server table

  1. #1
    Junior Member
    Join Date
    Jan 2012

    tune performance of a SQL Server table

    I have an assignment that : 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 temporary table that stores the described relationship :
    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-01     1.21
          1       2012-01-08     1.22
          2       2012-01-19     3.55
          2       2012-01-15     3.60
          3       2012-01-04     5.15
          3       2012-01-12     5.14
    To query for the effective price of an AssetId is simple, but it takes long time to calculate .

    How do I tune the table to retrieve the effective price (most recent price) quickly?

  2. #2
    Stick with this schema and create an non-clustered index on EffectiveDate and AssetId :
    CREATE INDEX    IX_AssetPrice_EffectiveDate
                    ON AssetPrice (EffectiveDate, AssetId) INCLUDE (Price)
    DECLARE @AssetId int = NULL, @EffectiveDate datetime = '2012-01-11'
    SELECT  AssetPrice.AssetId, AssetPrice.Price, AssetPrice.EffectiveDate
    FROM    AssetPrice
    JOIN    (
                SELECT  AssetId, MAX(EffectiveDate) EffectiveDate
                FROM    AssetPrice
                WHERE   EffectiveDate <= @EffectiveDate AND
                        (AssetId = @AssetId OR @AssetId IS NULL)
                GROUP BY AssetId
            ) Effective
            ON  AssetPrice.AssetId = Effective.AssetId AND
                AssetPrice.EffectiveDate = Effective.EffectiveDate
    WHERE   (AssetPrice.AssetId = @AssetId OR @AssetId IS NULL)

+ Reply to Thread

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts