No Clustered Index

Belias

hi everyone, as you can imagine from my recent threads, i'm messing with a test db in order to investigate indexes and SIFTs occupation/performance in a 4.01 sql database (45GB)

in "SQLIndex" thread i recently posted here, i learnt something about bookmark lookup on clustered indexes and some other stuff, therefore i started to take a look to table 17 (gl entry) indexes in NAV.
The first thing i noticed is that there isn't any clustered index for this table :? why?I expected to find "Entry No." field as a clustered index, but it's not so...

I have also taken a look at "key" system table and i found that only 15 tables have a clustered index (12 of them are not standard tables)... :-k

As i couldn't see any note about the disabilitation of the CI, i tried to restore a std 401 database on my pc...No CI at all in the restored database (note that i restored the .fbk file of the product CD).

My first thought is: should i attach the mdf file instead of restoring the .fbk on a newly created SQL Database?
My second thought is: does the .fbk file ships the clustered indexes out of the box?

Thanks in advance!

Belias

F**K to me! ](*,) ](*,) ](*,) ](*,)
the solution was in my hands!Thanks mibuso another time! (i couldn't find this topic with search...maybe it was at page 4 or so...)
http://www.mibuso.com/forum/viewtopic.php?f=7&t=11653&hilit=clustered

stryk

Have in mind that the missing "Clustered" flag in table "Key" does not necessarily mean there are no Clustered Indexes!

In NAV 4 this flag is usually not set, but the CI exist anyway (except for one early NAV 4.0 version - there was indeed a bug).

You could search for "Heaps" (= tables without CI) in SSMS using this query:

use [Database]
go
select object_name([id]) as [Heap Tables] from sysindexes where indid = 0
go

Belias

Thanks for the query, i've already taken a look to some tables in SSMS, and there weren't clustered indexes...i'll check it with your query, but i think there are no CI.
AFAIK this db was firstly created in 4.0 and then tech upgraded to SP1 (i am not sure because this customer had navision before i could even think than nav exists )
obviously, my "older" colleagues are on holiday
there are about 1000 heaps and about 260 clustered (indid=1)
i think there's a bug

stryk

Yep, seems like your db is affected by this early NAV 4.0 bug ](*,)

Once I created a little CU (50101) to fix this - please find it herwith.
Here I'm using a Timer-Control to set the CI in batches, allowing to COMMIT inbetween (else NAV would handle all in one HUGE transaction).

Please check it out! (No warranty, No guarantee, No support - USE AS IS)

OBJECT Codeunit 50101 Fix Clustered Index 4.0.1
{
  OBJECT-PROPERTIES
  {
    Date=18.07.07;
    Time=22:20:36;
    Modified=Yes;
    Version List=FCI1.00,SSI;
  }
  PROPERTIES
  {
    SingleInstance=Yes;
    OnRun=BEGIN
            LastTabNo := 1;

            IF ISCLEAR(Timer) THEN
              CREATE(Timer);
            Timer.Interval(500);   // Batch Interval
            Timer.Enabled(TRUE);
          END;

  }
  CODE
  {
    VAR
      Timer@1000000000 : Automation "{3B311C81-140E-11D5-8B18-00D0590B4EC5} 1.0:{3B311C92-140E-11D5-8B18-00D0590B4EC5}:'Navision Timer 1.0'.Timer" WITHEVENTS;
      LicPermission@1000000006 : Record 2000000043;
      KeyRec@1000000003 : Record 2000000063;
      ObjectRec@1000000002 : Record 2000000001;
      Object2Rec@1000000001 : Record 2000000001;
      i@1000000004 : Integer;
      LastTabNo@1000000005 : Integer;

    PROCEDURE SetClustered@1000000001(NoOfTables@1000000000 : Integer);
    BEGIN
      CLEAR(ObjectRec);
      IF ObjectRec.RECORDLEVELLOCKING THEN
        ObjectRec.LOCKTABLE
      ELSE
        ObjectRec.LOCKTABLE(TRUE,TRUE);

      i := 0;
      ObjectRec.SETRANGE(Type, ObjectRec.Type::Table);
      ObjectRec.SETRANGE(ID, LastTabNo, 99999999);
      IF ObjectRec.FINDSET(TRUE) THEN BEGIN
        REPEAT
          LicPermission.GET(ObjectRec.Type, ObjectRec.ID);
          IF LicPermission."Modify Permission" = LicPermission."Modify Permission"::Yes THEN BEGIN

            Object2Rec := ObjectRec;

            CLEAR(KeyRec);
            KeyRec.SETRANGE(TableNo, ObjectRec.ID);
            KeyRec.SETRANGE("No.", 1, 99);
            KeyRec.SETRANGE(Enabled, TRUE);
            KeyRec.SETRANGE(Clustered, TRUE);
            IF KeyRec.ISEMPTY THEN BEGIN
              i := i + 1;
              KeyRec.GET(ObjectRec.ID,1);
              KeyRec.Clustered := TRUE;
              KeyRec.MODIFY;
            END;

            ObjectRec.Modified := Object2Rec.Modified;
            ObjectRec.Date := Object2Rec.Date;
            ObjectRec.Time := Object2Rec.Time;
            ObjectRec.MODIFY;
          END;
        UNTIL (ObjectRec.NEXT = 0) OR (i = NoOfTables);
        LastTabNo := ObjectRec.ID;
        COMMIT;
      END;

      IF (i = 0) THEN BEGIN
        Timer.Enabled(FALSE);
        MESSAGE('Finished.');
      END;
    END;

    EVENT Timer@1000000000::Timer@1(Milliseconds@1000000000 : Integer);
    BEGIN
      SetClustered(10);  // Specify the "No. Of Tables" to be changed per batch
    END;

    EVENT Timer@1000000000::TimerError@2(ErrorString@1000000000 : Text[1024]);
    BEGIN
    END;

    BEGIN
    {
      Created clustered index on all tables with no clustered index.
    }
    END.
  }
}

Belias

thanks 4 the codeunit...i'll check it out (i tried to set the entry no. of gl entry manually, but after 15 minutes i gave up...i'll do it this evening before leaving!)

Eugene

A table can have only one clustered index or no clustered index at all.
I've read an advice somewhere that all tables in MS SQL better have a clustered index (though it does not have to be the primary key of a table but usually it is its primary key). The reason for that was the way MS SQL handles tables that have no clustered index - as table is modified frequently MS SQL creates too many pointers from one place to another without "compacting" the index.

What is clustered index ? - it is the index that tells MS SQL to physically save data in the order of that index. Well it does not mean all records are physically saved in the order of that index but it means the chunks of records (clusters) each have records saved physically in the order of the index though each chunk/cluster can be at any place on the disc so chunks themselves do not go one after another in the order of the index. Still it helps alot to read and find data by cluster index very fast.

Belias

Eugene wrote:

A table can have only one clustered index or no clustered index at all.
I've read an advice somewhere that all tables in MS SQL better have a clustered index (though it does not have to be the primary key of a table but usually it is its primary key). The reason for that was the way MS SQL handles tables that have no clustered index - as table is modified frequently MS SQL creates too many pointers from one place to another without "compacting" the index.

What is clustered index ? - it is the index that tells MS SQL to physically save data in the order of that index. Well it does not mean all records are physically saved in the order of that index but it means the chunks of records (clusters) each have records saved physically in the order of the index though each chunk/cluster can be at any place on the disc so chunks themselves do not go one after another in the order of the index. Still it helps alot to read and find data by cluster index very fast.

I can't get the point of your post... :-k you're right, but i've never asked it...i know what is a clustered index and i know that nav tables should have it...

Belias

i recently have done this upgrade together with some bucket removal from a 40GB database...it have been cut down to 4.5GB...WTF?!?!i can't really get it... ](*,)
EDIT: from nonclustered to clustered: from 40GB to 9GB
from "all buckets on" to "less buckets on" (and also rebuild of sift in order to avoid the bug in calculation): from 9GB to 4.5GB

stryk

Belias wrote:

i recently have done this upgrade together with some bucket removal from a 40GB database...it have been cut down to 4.5GB...WTF?!?!i can't really get it... ](*,)
EDIT: from nonclustered to clustered: from 40GB to 9GB
from "all buckets on" to "less buckets on" (and also rebuild of sift in order to avoid the bug in calculation): from 9GB to 4.5GB

I think this example highlights the importance of Index and SIFT optimization!
Without a CI the data is written overall in the database - wherever there's some free space, thus, you have - had- a maxium degree of fragmentation, hence, the objects allocate much too much space. Once this chaos has been organized, less space is consumed.
Further your example shows, how much crap-data is produced by standard SIFT structures ...

Belias

the strange thing is that another database, which has more transcations per day and more users, went from 42 to 30 gb...objects are nearly the same, except that one is localized Czech and one is localized Austrian... :-k

stryk

Well, it is difficult to predict how the DB will fragment without CI ... curious anyway ...