mibuso.com
Home› SQL Performance

Simple Question SQL Index

BeliasBelias Member Posts: 2,998
edited 2010-06-22 in SQL Performance
Nav 2009sp1, sql2005
hi everyone, i think this is a very simple question, i think: i want to create a new key on 2 fields, that are both option type --> not selective.
There are no sumindexes on this index and i wanted to set the key like this:
Key: OptionField1,Optionfield2
SQLIndex: Primarykeyfield,OptionField1,OptionField2 (the default index would be OptionField1,OptionField2,Primarykeyfield)

Is this modification effective?or is it pointless?is it risky? (i know about some issues about sqlindex property)
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog

Answers

  • krikikriki Member, Moderator Posts: 9,121
    A new index is only useful if your filters retain few records (5% or less of the total records in the table), otherwise SQL will NOT use the index and keep using the clustered index.
    An example:
    T21:"Customer Ledger Entry" with 1.000.000 records
    A filter on "Open" in is not useful if you want all the closed records (probably 990.000 records).
    But if you want all "Open" records (about 10.000), the filter can be useful (but it is not guaranteed).

    So if you think it might be useful, create it and see if SQL really uses it.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • David_SingletonDavid_Singleton Member Posts: 5,479
    Well really it depends on the version. In the newer versions, SQL really has no choice of what index to use, it works the same as Native, you tell SQL what to use.

    So if you plan to filter on these fields and its a big table then you need the indexes irrespective of how selective they may be.
    David Singleton
  • BeliasBelias Member Posts: 2,998
    David Singleton wrote:
    Well really it depends on the version. In the newer versions, SQL really has no choice of what index to use, it works the same as Native, you tell SQL what to use.

    So if you plan to filter on these fields and its a big table then you need the indexes irrespective of how selective they may be.
    really? i tried to do a client monitor on my query (which is a dataitem of a report with sourcetableview as my new index, with filters according the index) and nav actually does a clustered idx seek...which is not the new index, obviously... :-k
    i also thought that nav (SQL) would have follow my instruction, but it didn't...
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • DenSterDenSter Member Posts: 8,307
    Did you use that new key in a SETCURRENTKEY statement?
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • BeliasBelias Member Posts: 2,998
    No, because as i said, there is the key already set in the sourcetableview of the dataitem...Don't tell me that i have to explicitly use SETCURRENTKEY :shock: [-o<
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • DenSterDenSter Member Posts: 8,307
    As long as the ORDER BY clause matches the index exactly it should be forced to use the index. If the ORDER BY clause doesn't match exactly, it often reverts to a clustered index scan.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • David_SingletonDavid_Singleton Member Posts: 5,479
    I think the person that made these Dynamic Cursors changes in Navision was reading the Harry Potter books whilst coming up with this solution. :mrgreen:
    David Singleton
  • BeliasBelias Member Posts: 2,998
    :mrgreen:
    I couldn't post this yesterday, (no connection) sorry...
    My sql plan said Clustered index seek, not scan...i have to retry the test, maybe there was some filter on the primari key, too... :-k
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
Sign In or Register to comment.