Retrieving Data from an Audit Table

Retrieving data from an audit table.

Retrieving information from a history table for a given point in time is a common data task. For example, using a table that contains all historical prices, what where the prices of all products as they were 30 days ago? There are a number of ways to different methods to obtain this information from the audit table. In this article, I compare the performance of five such methods for varying amounts of historical data.

Data

Our sample set consists of two tables: a Products table that holds information about products and a PriceHistories table that holds all historical prices for those products.

Below are the scripts to create and populate the two tables.

–Products table

create table dbo.Products(

  ProductID                  int            not null,

  Name                       nvarchar(50)   not null,

  Description                nvarchar(2000)     null,

  constraint PK_Products

    primary key(ProductID)

)

go

create unique index AK_Products_1 on Products(Name)

go

–PriceHistories table

create table dbo.PriceHistories(

  PriceHistoryID             int                 not null identity(1,1),

  ProductID                  int                 not null,

  Price                      decimal(8, 2)       not null,

  DateEntered                datetime            not null,

  constraint PK_PriceHistories

    primary key(PriceHistoryID),

  constraint FK_PriceHistories_ProductID

    foreign key (ProductID)

    references dbo.Products

)

go

create unique index AK_PriceHistories_1 on dbo.PriceHistories(ProductID, DateEntered) include (Price)

go

create index IX_PriceHistories_1 ON dbo.PriceHistories (DateEntered) include (ProductID,Price)

– Populate the products table

begin tran

declare @x int

select @x = 0

while @x < 100

begin

  select @x = @x + 1

  insert into Products(ProductID, Name)

  select @x, ‘Product ‘ + cast(@x as nvarchar)

end

commit

go

– Populate the PriceHistories table

begin tran

declare @x int

select @x = 0

while @x < 1000

begin

  select @x = @x + 1

  begin try

    insert into PriceHistories(ProductID, Price, DateEntered)

    select rand()*100 + 1, rand(),dateadd(dd, 0 – rand() * 1000, getdate())

  end try

  — Retry in case the record is not unique

  begin catch

    while @@rowcount = 0

    begin

      insert into PriceHistories(ProductID, Price, DateEntered)

      select rand()*100 + 1, rand(),dateadd(dd, 0 – rand() * 1000, getdate())

    end

  end catch

end

commit

go

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |