Rebuilding SIFTs, building on Waldos idea

Mauddib

Waldo once posted an idea for using a report or codeunit to rebuild SIFT indexes. It was something along the line of:

Using the virtual KEY table uncheck the "MaintainSIFTIndex" option, modify, check it again, modify. You now have a completely clean SIFT table free of zeros, fragmentation etc etc.

The main issue however is that if you have anyone logged in they get the "Another user has modified the definition....." error when they use this table. Alas for me we have 30 users that need to be logged in constantly and run schedulers etc. This means restarting all 30 if this procedure is done.

However rebuilding the SIFT tables on even a small but busy table (sales line table) has improved one of my scheduled jobs from 4 hours to 15 minutes. A massive improvement. (Only one of our keys on that table has sift indexes with only two of its levels activated but its the key thats used when you do Item.CALCFIELDS("Qty. On Sales Order"))

So the question is, how to periodically, maybe weekly or monthly, cause a rebuild of such tables but without triggering the definition modification error?[/quote]

kine

Best will be to use SQL tools to make the maintenance. Under SQL 2005 you can do what you need without blocking the tables...

kriki

[Topic moved from Navision forum to SQL General forum]

stryk

Hmmm ... I disagree ... IMHO it's not a good idea to tick/untick the "MaintainSIFTIndex" flag within the "Key" table:

If you have optimized your "Buckets" (see http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx) this fiddling with the flag will reset to default - insufficient - settings ](*,)

To maintain the SIFT table you should run a weekly cleanup, e.g. using this procedure http://www.mibuso.com/dlinfo.asp?FileID=812
and peridically rebuild all indexes, e.g. using the SQL Maintenance Plan!

Mauddib

stryk wrote:

If you have optimized your "Buckets" this fiddling with the flag will reset to default - insufficient - settings ]

The object has always, so far in my experience, remembered the SIFT bucket settings after playing with this setting on the object. Nothing has ever returned to default on me.

I assume you are referring then to the covering indexes that you recommend on the SQL server side. I do not, at present, have any of these.

stryk

No. If you set "MaintainSIFTIndex" to FALSE and save the objects, the SIFT Tables are dropped including everything.
If you re-open the table object again to set "MaintainSIFTIndex" to TRUE, then the SIFT buckets are enabled by default - wrong - settings.

At least this happens when you do that directly in "Object Designer"; haven't tested that when doing it via C/AL in the "Key" table, I guess the results will be the same ... maybe you could test ...

Waldo

I don't know why, but i felt a littlebit obligated to answer on this post .

The post where Mauddib is talking about is this one.
As you can see, I did warn about the SIFT levels, as Jörg also did.

I took the libery to test this myself. This is my first codeunit (Please, don't comment on the code ... it's midnight at the moment ):

IF recKey.FINDSET THEN
  REPEAT
    IF recKey.TableNo = 17 THEN BEGIN
      recKey.VALIDATE(MaintainSIFTIndex, FALSE);
      recKey.MODIFY;
      COMMIT;
    END;
  UNTIL recKey.NEXT = 0;

No, SETRANGE is not possible on the Key table (at least not in my database).
After running this codeunit, all SIFTs from G/L Entry table are gone... .

After that, I run this code (Second codeunit):

IF recKey.FINDSET THEN
  REPEAT
    IF recKey.TableNo = 17 THEN BEGIN
      recKey.VALIDATE(MaintainSIFTIndex, TRUE);
      recKey.MODIFY;
      COMMIT;
    END;
  UNTIL recKey.NEXT = 0;

The SIFTs are back ... in the SAME way as they were deleted. Same levels, same buckets, no more, no less!! So I guess Mauddib is right ... (I didn't expect this myself ).

This is a different behaviour then doing this from the object designer, but interesting enough to build some kind of "re-sift-engine" .

DenSter

It's a nit similar it seems as the difference between disabling a key from the table designer and doing it by disabling a keygroup. The first wipes out all SIFT, and it doesn't come back when you re-enable the key. From disabling keygroup though it does restore all SIFT to where it was before.

The really weird part.... when you disable a key (directly or from a keygroup), all SIFT definition is removed from the text object file, so there must be some sort of references somewhere else.

Waldo

It's weird indeed. May be there's a seperate 'virtual internal table' that is storing the SIFTLevels ... :-k

Mauddib

Waldo wrote:

I don't know why, but i felt a littlebit obligated to answer on this post

Hehehe I actually regretted putting your name in the title after I did it. I realise your original post was more of a "thinking out loud" and my title here suggested it was somethign you were putting your name to as a definite course of action. Sorry if I caused any iritation

Waldo wrote:

The SIFTs are back ... in the SAME way as they were deleted. Same levels, same buckets, no more, no less!! So I guess Mauddib is right ... (I didn't expect this myself ).

Hehehehe my knowledge in Navision may be much more limited than you and Herr Stryk. But I do have a rule "dont say anything until you are sure you are correct"

Waldo wrote:

but interesting enough to build some kind of "re-sift-engine"

It is. But alas not for me unless I find some way around the "Another user has modified the definition....." error.... which alas doesnt look likely.

Waldo

No irritation here . This is a forum afterall.

Waldo

I wrote a blogpost about the Key-table ... if you're interested.
8)

ajhvdb

Waldo wrote:

I wrote a blogpost about the Key-table ... if you're interested.
8)

Very good info. Thx.