Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    9

    Unanswered: Witch order of columns is the best for an index

    I want to make an index with the following columns, actually together they constitute the PK of the table so the index is created automatically.

    The question is witch sort order is the best to have if I want to fetch all rows for one date and one resource? And why?

    WHERE Resource = ‘Car 1’ AND Date = ‘2013-03-03’


    Resource, Date, Time

    Or

    Date, Time, Resource

    Below is an example with tree resources, but in reality there can be a lot more and also years of dates.

    Car 1, 2013-03-03, 10.00

    Car 1, 2013-03-03, 11.00

    Car 1, 2013-03-03, 12.00

    Car 1, 2013-03-04, 10.00

    Car 1, 2013-03-04, 11.00

    Car 1, 2013-03-04, 12.00

    Car 2, 2013-03-03, 10.00

    Car 2, 2013-03-03, 11.00

    Car 2, 2013-03-03, 12.00

    Car 2, 2013-03-04, 10.00

    Car 2, 2013-03-04, 11.00

    Car 2, 2013-03-04, 12.00

    Car 3, 2013-03-03, 10.00

    Car 3, 2013-03-03, 11.00

    Car 3, 2013-03-03, 12.00


    Car 3, 2013-03-04, 10.00

    Car 3, 2013-03-04, 11.00

    Car 3, 2013-03-04, 12.00



    2013-03-03, 10.00, Car 1

    2013-03-03, 10.00, Car 2

    2013-03-03, 10.00, Car 3

    2013-03-03, 11.00, Car 1

    2013-03-03, 11.00, Car 2

    2013-03-03, 11.00, Car 3

    2013-03-03, 12.00, Car 1

    2013-03-03, 12.00, Car 2

    2013-03-03, 12.00, Car 3

    2013-03-04, 10.00, Car 1

    2013-03-04, 10.00, Car 2

    2013-03-04, 10.00, Car 3

    2013-03-04, 11.00, Car 1

    2013-03-04, 11.00, Car 2

    2013-03-04, 11.00, Car 3

    2013-03-04, 12.00, Car 1

    2013-03-04, 12.00, Car 2

    2013-03-04, 12.00, Car 3

    Last edited by andy1234567; 06-19-13 at 10:06.

  2. #2
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Below is a test with 40,000 records.

    Create and populate a table with some dummy information

    Code:
    /*
    DROP table if it exists
    */
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ColumnOrder_DBForumsTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[ColumnOrder_DBForumsTest]
    
    
    /*
    Create a table for testing
    */
    CREATE TABLE [dbo].[ColumnOrder_DBForumsTest](
    	[Resource] [char](5) NOT NULL,
    	[ResourceDate] [date] NOT NULL,
    	[ResourceTime] [time](7) NOT NULL) ON [PRIMARY]
    
    
    /*
    TRUNCATE and test to verify table is empty
    */
    TRUNCATE TABLE ColumnOrder_DBForumsTest
    
    SELECT * FROM ColumnOrder_DBForumsTest
    
    
    /*
    Declare variables
    */
    DECLARE @I	INT
    DECLARE @RowsToInsert	INT
    DECLARE @ResourceDate	DATE
    DECLARE @Resource		CHAR(5)
    DECLARE @StartTime		TIME
    
    
    /*
    1 of 4 INSERT statements
    */
    SET @I=0
    SET @RowsToInsert = 10000
    SET @ResourceDate = DATEADD(D,-10000,GETDATE())
    SET @Resource = 'Car 1'
    SET @StartTime = '11:00:00.0000'
    
    WHILE @I<@RowsToInsert
    BEGIN
    
    INSERT INTO ColumnOrder_DBForumsTest
    VALUES (@Resource,@ResourceDate,@StartTime)
    
    SET @ResourceDate = DATEADD(D,1,@ResourceDate)
    SET @I = @I+1
    END
    
    
    /*
    2 of 4 INSERT statements
    */
    SET @I=0
    SET @RowsToInsert = 10000
    SET @ResourceDate = DATEADD(D,-10000,GETDATE())
    SET @Resource = 'Car 1'
    SET @StartTime = '12:00:00.0000'
    
    WHILE @I<@RowsToInsert
    BEGIN
    
    INSERT INTO ColumnOrder_DBForumsTest
    VALUES (@Resource,@ResourceDate,@StartTime)
    
    
    /*
    3 of 4 INSERT statements
    */
    SET @ResourceDate = DATEADD(D,1,@ResourceDate)
    SET @I = @I+1
    END
    
    SET @I=0
    SET @RowsToInsert = 10000
    SET @ResourceDate = DATEADD(D,-10000,GETDATE())
    SET @Resource = 'Car 2'
    SET @StartTime = '11:00:00.0000'
    
    WHILE @I<@RowsToInsert
    BEGIN
    
    INSERT INTO ColumnOrder_DBForumsTest
    VALUES (@Resource,@ResourceDate,@StartTime)
    
    SET @ResourceDate = DATEADD(D,1,@ResourceDate)
    SET @I = @I+1
    END
    
    
    /*
    4 of 4 INSERT statements
    */
    SET @I=0
    SET @RowsToInsert = 10000
    SET @ResourceDate = DATEADD(D,-10000,GETDATE())
    SET @Resource = 'Car 2'
    SET @StartTime = '12:00:00.0000'
    
    WHILE @I<@RowsToInsert
    BEGIN
    
    INSERT INTO ColumnOrder_DBForumsTest
    VALUES (@Resource,@ResourceDate,@StartTime)
    
    SET @ResourceDate = DATEADD(D,1,@ResourceDate)
    SET @I = @I+1
    END
    
    /*
    Test to verify data is populated
    */
    SELECT * FROM ColumnOrder_DBForumsTest
    Now that you have the table built, feel free to run some query races. Highlight the SELECT statement and hit CTRL-L to view the execution plan. Take note of the estimated subtree costs.

    Initially with no indexing

    Code:
    /*
    With No Indexes
    
    Table Scans
    */
    SELECT * FROM ColumnOrder_DBForumsTest
    SELECT * FROM ColumnOrder_DBForumsTest WHERE Resource = 'Car 1'
    SELECT * FROM ColumnOrder_DBForumsTest WHERE ResourceDate < '2000-02-01'
    Now we add a Primary Key

    Code:
    /*
    With a Primary Key 
    Resource, ResourceDate, ResourceTime
    */
    ALTER TABLE ColumnOrder_DBForumsTest ADD CONSTRAINT PK_Resources PRIMARY KEY ([Resource],[ResourceDate],[ResourceTime]) 
    
    /*
    Clustered Index Scans
    */
    SELECT * FROM ColumnOrder_DBForumsTest
    SELECT * FROM ColumnOrder_DBForumsTest WHERE ResourceDate < '2000-02-01'
    
    /*
    Clustered Index Seek
    */
    SELECT * FROM ColumnOrder_DBForumsTest WHERE Resource = 'Car 1'
    Drop the Primary Key and recreate using a different column order

    Code:
    /*
    Drop and Recreate Primary Key in new order 
    ResourceDate, ResourceTime, Resource
    */
    ALTER TABLE ColumnOrder_DBForumsTest DROP CONSTRAINT PK_Resources
    ALTER TABLE ColumnOrder_DBForumsTest ADD CONSTRAINT PK_Resources PRIMARY KEY ([ResourceDate],[ResourceTime],[Resource])
    Review the new execution plans

    Code:
    /*
    Clustered Index Scans
    */
    SELECT * FROM ColumnOrder_DBForumsTest
    SELECT * FROM ColumnOrder_DBForumsTest WHERE Resource = 'Car 1'
    
    /*
    Clustered Index Seek
    */
    SELECT * FROM ColumnOrder_DBForumsTest WHERE ResourceDate < '2000-02-01'

    For the query you supplied (SELECT * FROM ColumnOrder_DBForumsTest WHERE Resource = 'Car 1' AND ResourceDate='2013-03-03'), try a couple variations of the Primary Key. Pay attention to the subtree costs when items in the WHERE clause are near the top of the order.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    [QUOTE .. they constitute the PK of the table so the index is created automatically. The question is which sort order is the best to have if I want to fetch all rows for one date and one resource? And why? [/QUOTE]

    The first design error is called "attribute splitting" and you need to fix this immediately. The timestamp (aka DATETIME2(n) in T-SQKL dialect) is a single unit of temporal measurement.

    Next, when you index a temporal column, remember to use DESC sort order. Most temporal queries want to use recent data and not the stuff from the start of the company.

    The next question is how do you group the data? If the reporting and insertions are done by resources, put it first. If the data is handled by dates, put the timestamp first.

    Now test it both ways against real data.

  4. #4
    Join Date
    Dec 2007
    Posts
    9
    Celko

    You made assumptions about the code in the last post I made, and in this post you’re making more assumptions. The problem is that they are wrong.

    I have one tip for you and that is to try your assumptions against the real world and quit being like an arrogant kid.
    Last edited by andy1234567; 06-19-13 at 15:29.

  5. #5
    Join Date
    Dec 2007
    Posts
    9
    Thank you flyersfanjd, that will be very useful!

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Why do we make assumptions?

    You made assumptions about the code in the last post I made, and in this post you’re making more assumptions. The problem is that they are wrong.
    Sorry I was not able to read your mind. The poster is the person obligated to explain the problem. So, how were they wrong? Time is not ordered from present to past, moving in only one direction? A timestamp is not a temporal unit on the continuum? TIME is separate from the continuum? The entire ISO temporal model is wrong along with all of the last 40 years of temporal logic and temporal databases work?

    I have one tip for you and that is to try your assumptions against the real world and quit being like an arrogant kid.
    Gee, I should have used the sample data? But there was none! And for the record, I am famous for being an arrogant old fart who crushes children (less than 20 years with RDBMS, never wrote a standard, etc) for their ignorance.

  7. #7
    Join Date
    Dec 2007
    Posts
    9
    You are missing the point, you don’t need to make assumptions, you don’t need to try to solve the bigger problem because your convinced (even though it’s just an ego defence to protect your self from your feeling of being inadequate) that other people is not as good as you. You just need to answer the question, that’s all.

    You are just a caricature of a very insecure person, a person who lash out on whatever he see as a chance to weigh up his insecurity on somebody else’s expense. You are willing to distort reality in anyway you can in order to use this distortion as a weapon to try and put somebody down and in that contrast that you just created based on the distortion you feel quite good.

    The problem for people like this is that the fix only lasts for five minutes and then they are back to where they started again.

    So please quit answer my posts, I don’t need that type of help.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I see both andy1234567 and Celko making good points.

    Cellko is well known for both being very, VERY good at SQL and math in general and also for being very, VERY bad at social issues. He understands this, and can live with the costs and benefits of his choices.

    andy1234567 understands his problem, and somehow got very lucky that flyersfanjd made enough good assumptions to provide a useful answer. I certainly couldn't get to the point where I could contribute from what andy1234567 originally posted, so I didn't post anything.

    I've been working on trying to blunt some of Celko's social "rough corners" for just over 20 years (we started arguing on CompuServe for those who remember it). Those corners are a bit smoother after 20 years, but still quite hard on newcomers. While he can be quite irritating at times, Celko really does know what he's talking (often ranting) about... I think that it would be sheer folly to ignore him even when he is abrasive.

    I won't give either of you advice. You'd probably ignore it if I did. But I think that both of you can learn from the other.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    I am famous for being an arrogant old fart who crushes children (less than 20 years with RDBMS, never wrote a standard, etc) for their ignorance.
    Hmmm....not quite.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The quick answer is put the index in the order of most filtering if possible. Meaning if most of the queries can't supply that as input to their query then use the column that will be best known to all sql consumers.

Posting Permissions

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