Speed Issues associated with Dimension Use

twdavis · 2007-01-15

I need help!

recently converted Nav4.0 SP2 to SQL. This client has over used the Dimensions feature in Navision (13 dimensions). Experiencing 30 - 35 second posts of Sales Orders, Purchase Orders, production status changes, etc. I have run a SQL Profiler trace on one post of a Sales Order with one line item and noticed the following:

30 seconds lapse time
I see frequent executions of a stored procedure querying the "Ledger Entry Dimension" table for several of the Diemensions. These queries seem to be where the bulk of the lapse time is spent.

For example:

Exec sp_execute 108,17,2402102,2147483647,'EQUIPMENT'

The sql code is
SELECT TOP 1 NULL FROM "ForentaSQL"."dbo"."Forenta$Ledger Entry
Dimension" WITH (READUNCOMMITTED) WHERE (("Table ID"=@P1)) AND
(("Entry No_">=@P2 AND
"Entry No_"<=@P3)) AND (("Dimension Code"=@P4))

The 2nd parameter is the table id: 17 for G/L Entry, 3rd parameter is the lower limit coresponding to the entry number, 3rd Upper limit, 4th Dimension Code.

Can anyone tell me why the the 4th parameter appears to be the MAX integer value? Why is it reading the entire table above the first entry number?

Short of not using Diemnsions (not an option), what can I do to respolve the speed issues?

Has anyone else experienced this? How did you resolve the speed issue?

Help! Please!

kine · 2007-01-15

It is reading just first record (the "TOP 1" after SELECT). Check keys on the table, if there are correct keys with good selectivity for that query.

twdavis · 2007-01-15

the table has 4 fields and 2 indices. the first 3 fields are the primary key. I assume this is optimal.

If it is reading only on e record why does it indicate doing 26340 reads for a duration of 1260?????

kriki · 2007-01-15

Do you do a rebuild index at least each week and a rebuild SQL statistics each night?
Something else to speed up writing in the table:
Index "Dimension Code,Dimension Value Code" : don't maintain it in SQL. (DO NOT disable it, just property MaintainSQLIndex=No).

twdavis · 2007-01-15

Thanks for your response. Tried that last week, no improvement. (rebuilt index and rebuilt SQL statistics ). Am not doing it at this time.

Disabling the MAintainSQL Index would help the 'writes' but the problem seems to be in the Reads of the Ledger Entry Dimension during a POST.
I am willing to try anything at this point. I will set that property after lunch.

Any more suggestions?

kine · 2007-01-15

twdavis wrote:

the table has 4 fields and 2 indices. the first 3 fields are the primary key. I assume this is optimal.

If it is reading only on e record why does it indicate doing 26340 reads for a duration of 1260?????

Yes, but which field have biggest selectivity? Table No? How many values can have? How many dimensions codes you are using? How many different dimension values (through all dimensions)? What about changing the order of the fields on SQL index? May be that if you set the dimension value field as first field in the PK, it will select the correct segment of data much faster... but I do not know, because I do not know the answers to all questions... 8)

kriki · 2007-01-16

twdavis wrote:

Thanks for your response. Tried that last week, no improvement. (rebuilt index and rebuilt SQL statistics ). Am not doing it at this time.

Disabling the MAintainSQL Index would help the 'writes' but the problem seems to be in the Reads of the Ledger Entry Dimension during a POST.
I am willing to try anything at this point. I will set that property after lunch.

Any more suggestions?

The index I mentioned is ONLY used in case you COMPLETELY delete a dimension from your system. So:NEVER.

The best selectivity is in this case "Entry No.". But I would not change the order of the fields in SQL for a primary key.
You might try to add a new key "Entry No." (with property SQLIndex=Entry No.) and see how it performs (after a SQL Index statistics of course).
If you want to add an extra field to this key : or "Dimension Code" or "Table ID". Difficult to say which is the best. It depends on how much dimensions you have, how many are in a single Entry No.", how many different tables you have in use, ....

Speed Issues associated with Dimension Use

Comments

Categories