New keys

WaldoWaldo Member Posts: 3,412
edited 2007-03-20 in SQL Performance
Hi,
I would want your opinion.

I have created two extra indexes? Why? Because my customer is using NAV and Filters in the most exotic way...
The question is in which order you would place the fields... . I know, the most selective, but it's hard to say which are most selective... #-o

Index No. 1 on Sales Line table:
- No.
- Shipment Date
- Outstanding Quantity
- Type

I know No. is good, but what about the other fields?

Index No. 2 on Sales Line table:
- Shipment Date
- Production Group
- Shipment Day
- VendorNr

You would say "vendornr" is quite selective, but it isn't. They filter on it the way that they still get a very large number of records... , same for production group. In fact, the way they filter, you get about en even amount of records for all fields... ](*,) .
Is it a good idea to put a date first?

Eric Wauters
MVP - Microsoft Dynamics NAV
My blog

Comments

  • krikikriki Member, Moderator Posts: 9,094
    Waldo wrote:
    Index No. 1 on Sales Line table:
    - No.
    - Shipment Date
    - Outstanding Quantity
    - Type

    I know No. is good, but what about the other fields?
    In SQL, a date and a time are of type "Datetime", so not really good in an index. So best leave it at the end of the index. Most of the time, I don't even put it in the index because the gain for reading is low and I do gain some speed for writing the index (and not writing the field).
    Also a decimal in general is not really good for an index. (Only used it in Navision when I had to sort on it...)
    So in this case I would say : "No.","Type" as index. the other fields are of no use (in general).
    If you know that you only have 1 Type (e.g. Item), no need to put it in the index.
    You can try something for checking if "Shipment Date" is usefull: With the "SQL Server Management Studio" of SQL2005, you can check the selectivity-statistics of the index and if you see that the "shipment date" helps a lot, you can put it in the index. BUT only if you really use it to filter.

    Waldo wrote:
    Index No. 2 on Sales Line table:
    - Shipment Date
    - Production Group
    - Shipment Day
    - VendorNr

    You would say "vendornr" is quite selective, but it isn't. They filter on it the way that they still get a very large number of records... , same for production group. In fact, the way they filter, you get about en even amount of records for all fields... ](*,) .
    Is it a good idea to put a date first?
    First and for all : it is never a good idea to put the date first. (Except, maybe, if you don't have any other filter)

    What field type is Shipment Day? I suppose it is an integer with 7 possible values (1 for each day of the week).
    So also this one has not really a big selectivity. So I suppose you can choose between "Vendor No.","Production Group" or "Production Group","Vendor No.".
    After these 2 fields, I would say "Shipment Day","Shipment Date" OR "Shipment Day" OR "Shipment Date". This depends if you filter on it or not and how useful it is (see again selectivity-statistics).

    How to see selectivity-statistics IN SQL2005 (also useful on a SQL2000!).
    Go in the database => table => Statistics => select your key. Doubleclick on it=>another form opens => Details : "All Density"-column. The smaller the number the better.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    kriki wrote:
    How to see selectivity-statistics IN SQL2005 (also useful on a SQL2000!).
    Go in the database => table => Statistics => select your key. Doubleclick on it=>another form opens => Details : "All Density"-column. The smaller the number the better.

    I thought so about the dates, but in fact, the dates do give me some performance gain :-k ...

    Thanks for the tip about selectivity. Didn't know that...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Yes, dates in indexes can be very usefull. Discovered that again today. :mrgreen:

    It is always a matter of considerarion. Reads vs. Writes.
Sign In or Register to comment.