Disabling SIFTLevelsToMaintain

suvidha

Hi,
Our Client is running on 3.7.
We face performance issues quite often now.
I went tru most of the posts here and learnt that
If I have keys defined as below:

1.Key: G/L Account No.,Posting Date.
SIF : Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount.
SIFTLevelsToMaintain : {G/L Account No.},
{G/L Account No.,Posting Date:Year}…………..

2.Key: G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
SIF: Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount.
SIFTLevelsToMaintain : {G/L Account No.},
{G/L Account No.,Business Unit Code},
{G/L Account No.,Business Unit Code,Global Dimension 1 Code}……

In this case I can disable {G/L Account No.} in SIFTlevelsToMaintain of Key2.
If this is right…. Y is this not taken care in base versions???? I see many buckets being repeated like this in base 3.7.????
Is it taken care in higher versions???
First of all is my understanding regarding this rite???? :!:

stryk

Hi!

First of all: No, the SIFT Buckets are not optimized in any version of NAV; but they are replaced by VSIFT since version 5.0 SP1.

There's a lot one could say about SIFT tuning etc., but I recommend this proceeding:

Referring to your example in T17 "G/L Entry":
Key: G/L Account No., Posting Date
SIFT Buckets available: 0, 1, 2, 3, 4, 5
SIFT Buckets enabled (default): 1, 2, 3, 4

Bucket 0 (GRANT TOTAL) is disabled and should NEVER be enabled.
Bucket 5 is also disabled. 5 "aggregates" on Primary Key level (the "Entry No." is added!) hence there is NO aggregation. Enabling 5 would mean to create one SIFT record for each record in T17. Thus, also Bucket 5 must NEVER be enabled.

The first level of aggregation which is really needed is Bucket 4. All higher levels (1, 2, 3) can be calculated from Bucket 4.
Means you could disable Buckets 1, 2, 3 only leaving Bucket 4 enabled.

As a general "rule of thumb" one could say:

"Disable all SIFT Buckets, except the one before the last one (the PK Bucket)"

Only if you encounter problems with this single Bucket - if you got proof with SQL Profiler - then you should enable the next higher Bucket (but have in mind that there are other tricks to fix this).

The consequenses of this are:
- Shorter "SIFT Trigger" code, faster SIFT processing
- Reduction of number of records in SIFT tables = smaller indexes
- Faster Read and Write performance
- Reduced blocking conflicts

This optimization should be implemented in all large or heavily used tables. For tables which just contain few records; e.g. less than 10000 you may consider to disable SIFT at all (MaintainSIFTIndex = FALSE).

P.S.: If you have a "Date" field in the key you should only enable the "Day"-aggregation. If there is a "DateTime" field you should also start aggregatin on "Day"-level, NOT "Milliseconds" etc.!

P.S.P.S.: The remaining aggregation level - after optimization - is actually the same level of aggregation used in VSIFT.

suvidha

That was very clear....
Thanks a lottt.....

suvidha

Hi stryk,
i have observed in the client Db, In Item ledger entry table

Key: Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date
SumIndexFields: Quantity,Invoiced Quantity

The maintained Bucket No. and SIFT Levels are:

4. Entry Type,Item No.,Variant Code,Drop Shipment
5. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code
9. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day,Entry No

Bucket no. 9 is "aggregates" on Primary Key level

but as u said, this last SIFT level should never be enabled...
Now how can i find y this was enabled during some customization
How to identify if this is useful or not... :?:

stryk

suvidha wrote:

Hi stryk,
i have observed in the client Db, In Item ledger entry table

Key: Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date
SumIndexFields: Quantity,Invoiced Quantity

The maintained Bucket No. and SIFT Levels are:

4. Entry Type,Item No.,Variant Code,Drop Shipment
5. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code
9. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day,Entry No

Bucket no. 9 is "aggregates" on Primary Key level

but as u said, this last SIFT level should never be enabled...
Now how can i find y this was enabled during some customization
How to identify if this is useful or not... :?:

Well, the only chance to find out who has changed this and when is to compare the object wit a previous version - so probably you'll never find out whos has done this and why (anything in the "Documentation-Trigger"?) ... :-k

But this is definitely not useful:

If you have this Bucket enabled, a SIFT-record will be created per "Item Ledger Entry" record.
You could check with this TSQL:

select count(*) from dbo."CRONUS 403$Item Ledger Entry"
select count(*) from dbo."CRONUS 403$32$0" where "bucket" = 9

Both figures should be the same.
So what would happen is this:
If anyone calculates the "Quantity" or "Invoiced Quantity", having set a filter on the "Entry No." then NAV would recognize the existing SIFT Bucket (9) and probably fire this query (or similar):

select sum(s12), sum(s14) from dbo."CRONUS 403$32$0"
where "bucket" = 9 and "f2" = ... and "f1" = 12345

Where "f1" is the "Entry No.". If the Bucket (9) would not exist, then NAV would directly query the "Item Ledger Entry" table:

select sum("Quantity"), sum("Invoiced Quantity") from dbo."CRONUS 403$Item Ledger Entry"
where "Item No_" = ... and "Entry No_" = 12345

The results should be identical.
Hence, the "PK Bucket" is just creating a hell of records, expanding the SIFT-table tremendously, causing slow performance.

suvidha

I found 2 keys with Pk bucket in Item Ledger entry!!!!
Thanks a lot... i am going to disable these hoping the performance will be better:)

DenSter

Don't expect miracles. Your system is not going to all of a sudden be lightning fast just for disabling that SIFT level.

garak

SIFTS are one point to start.
Others are the indizes, table structure, the C/AL source self, the Hardware (RAM, HDD, RAID, SAN,CPU), the SQL Server config self, the design of forms and Reports (for example many FLOW fields on forms, filters which are not good for the "order by" (setcurrentkey) statement, Blocks or "stupid" users or or or ...
So, fist check why the system is slow (you can check this, f.e. with Perfmon, SQL Profiler, or the extended Client Monitor and Pivot Tables in Excel).

Here in the forum are many posts about this. One is, i hope it was this post, this one viewtopic.php?f=34&t=13154
But there are also many others. read this posts before (i know could be a lot of work)...

viewtopic.php?f=34&t=24392
viewtopic.php?f=34&t=25676

Regards

bored04

this is an interesting discussion.. thanks for sharing

simulation rachat de credit

garak

please and welcome.
Also read the other post in the "SQL Perfomance" forum