Sift Tables

NavStudentNavStudent Member Posts: 399
edited 2007-03-02 in SQL Performance
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
my 2 cents

Comments

  • ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • krikikriki Member, Moderator Posts: 9,110
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.