Sift Tables

NavStudent · 2007-02-04

Hello I was reading this column on persisted columns on sql 2k5.
This looks like a solution for sift tables.
You could instead of sift table use persisted fields.

Anybody ever thought about this?

Here is an example

CREATE TABLE [dbo].[Invoice_Table](
     [Invoice#] [int] IDENTITY(1,1) NOT NULL,
     [Invoice_Amount] [money] NOT NULL,
     [Invoice_Freight] [money] NOT NULL,
     [Invoice_Total] AS ([Invoice_Amount]+[Invoice_Freight]) PERSISTED
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Invoice_Total_Idx] ON [dbo].[Invoice_Table]
(
     [Invoice_Total] ASC
) ON [PRIMARY]

In navsion world the nonclustered index would be [invoice no,Invoice_Total] . And the persistant field will equal the actual field instead of adding two fields. That way summing up on the value is done on the key, and not on the data. That way you don't need sift tables at all.
:-k

Here is an article about this.

http://www.sql-server-performance.com/bm_adding_indexes_persisted.asp

ara3n · 2007-02-04

Hello Navstudent.
I ran test on this on sql and running a sum statement on PERSISTED fields is slower than the actual fields.

Here are the steps I took.

In First statement I created the table. with the key.

CREATE TABLE [dbo].[Invoice_test]( 
     [Invoice] [int] IDENTITY(1,1) NOT NULL, 
     [Invoice_Amount] [money] NOT NULL, 
     [Invoice_Total] AS ([Invoice_Amount]) PERSISTED 
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Invoice_Total_Idx] ON [dbo].[Invoice_test] 
( 
	 [Invoice],
     [Invoice_Total] ASC 
) ON [PRIMARY]

Then I populated the tabel with 100K random records

declare @t int
set @t=1000
while @t <= 100000
begin
  insert [Invoice_test] (Invoice_Amount) values (rand()*26 + 65)
  select @t=@t + 1
end
go

Ran the following statement to sum on actual field.

DBcC dropcleanbuffers
dbcc freeproccache 
GO

select sum(Invoice_Amount) AS INVOICE from Invoice_test
where Invoice > 50000 and Invoice < 60000
go

This took 400 ms.

Then I ran

DBcC dropcleanbuffers
dbcc freeproccache 

select sum(Invoice_Total) AS INVOICETOTAL from Invoice_test
where Invoice > 40000 and Invoice < 50000

This took 183 milliseconds. Almost in half.

hmmmm

I'll next create a navision table and will post the results. I'm doubt sift table will be slower.

ara3n · 2007-02-04

I ran some more test., by creating a clustered key on the table update statistic etc on the table

The results are now the opposite. Persisted field take longer than actual fields. About 2 and half times longer than the actual fields.

Hope this answers your question.

kriki · 2007-03-02

[Topic moved from Navision forum to SQL Performance forum]

Sift Tables

Comments

Categories