Clustered Problem

Mally
Mally Member Posts: 128
edited 2009-09-15 in NAV/Navision Classic Client
Dear Concern
I have 2 DB's both in SQL both DB's having table "Payment Terms"(ID 3).In 1 DB the property "Clustered" of key in table 3 is yes and another DB the property "Clustered" of key in table 3 is No.

Can anybody tell me whats the difference between these?Any performance issue is related to this property?

Comments

  • kine
    kine Member Posts: 12,562
    It depends on history of the objects. This property was introduced in one servicepack for NAV 4.00 (I do not know now if it was SP1, SP2 or SP3). If the objects are older, they do not have this property set. If they are newer, they have this property correctly set. If the property is not set in the object, but you are using NAV 4.00SP3, the first key will be created as clustered on SQL. (but there are exceptions and sometime there is no clustered index on the SQL=> very bad performance of whole system).
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garak
    garak Member Posts: 3,263
    The Clustered property comes with Navision 4.0 SP1.

    If the table is very large and there are many datas in it, and there is no CI, u become a very bad performance as kine said. the reason is, the SQL Server stored the datas not in a sorted structure, it stored it like a heap.
    Do you make it right, it works too!
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Payment terms is a very small table, so it should not be too critical. Worry more about hot tables.
    David Singleton
  • Belias
    Belias Member Posts: 2,998
    Search is your friend :wink:
    http://www.mibuso.com/forum/viewtopic.php?f=33&t=36466
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Mally
    Mally Member Posts: 128
    Dear Concern
    Anybody please tell me Hot Tables includes whic tables? Please name them?This will help me in solving my problem.
  • Belias
    Belias Member Posts: 2,998
    edited 2009-09-15
    Hot tables = tables with a lot of *...examples are journals, sales line, g/l entries
    *EDIT: write transactions
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Mally wrote:
    Dear Concern
    Anybody please tell me Hot Tables includes whic tables? Please name them?This will help me in solving my problem.

    This list is impossible to make, by definition, every implementation will be different, there are a hand full of "typical" hot tables, but there is no general answer to this question.

    Anyone capable of tuning a SQL database will be able to look at your datbase and give you this answer. But if you get any hard answer to this question, then that answer is wrong. Possibly the only table that could always be considered hot is the G/L Entries.
    David Singleton

Categories