Item Ledger Entry Table works very slow in SQL Server

fortius_cva

Hi all,

We have upgraded one our client navision version from 3.7 To 6.0 and also migrated the Navision Native Database to SQL Server 2008.

While accessing the table 32(Item Ledger Entry) which has 40 keys is very slow in SQL Server when compared to Navision Database. some times not even processing the request.

This is not happening the Value Entry Table which occupies more than the size of ledger entry but with only having 20 keys in it.

i have done optimization to table "Item Ledger Entry". Still the problem exixts.

Could anyone suggest me a solution or any activity which can improve the performance.

JedrzejT

Search the forum for optimization, performance, there is a lot of topic about this - because i think your optimization isn't over if you have 40 keys in table.
What means "Accesing" table? Open list? (maybe you have a lot FlowFields on form)

rmv_RU

Some theories:
In Navision 5.0 have changed the mechanism of work with SIFT - now instead of tables and sql triggers use indexed views.
Unfortunately, this is a positive innovation increase the differences between Native DB or SQL version. The key works perfectly in NativeDB can greatly hinder the work of the SQL version and vice versa.
As example key in "Sales Line" table:
Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Bin Code", "Document Type", "Shipment Date" c sumindexfield "Outstanding Qty. (Base)" and MaintainSiftIndex on each of the combinations:
Type, "No."
Type, "No.", "Variant Code"
Type, "No.", "Variant Code", "Drop Shipment"
Type, "No.", "Variant Code", "Drop Shipment", "Location Code"
Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Bin Code"
Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Bin Code", "Document Type"
Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Bin Code", "Document Type", "Shipment Date"

In Native DB field value "Outstanding Qty. (Base)" physically stored together with the index.
In the SQL version to 5.0 values are stored in a separate table with each .
In SQL versions starting with 5.0, there is one indexed view (what is indexed - also need to check) with fields
Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Bin Code", "Document Type", "Shipment Date", SumIndexField1, .. SumIndexFieldN and the primary key Type, "No.", "Variant Code", "Drop Shipment", "Location Code", "Bin Code", "Document Type", "Shipment Date". Request to execute when you call code SalesLine.CALCSUMS ("Outstanding Qty. (Base)") will look like:
select sum (["Outstanding Qty. (Base)"]) from IndexView where ....( filters).
The speed of its work will depend on the number of superimposed filters, the number of entries in the table and the selectivity of the key.

In your case I would have done:
1. Check that SIFT really based on indexed views (possibly indexed view are disabled in the server settings).
2. Reviewed the requests and the selectivity of keys used. General Rules: Long keys - evil. Fields often used in the calculation SIFT should be moved to the beginning of the key (for example, Posting Date field at the end of the key decrease the generation speed of reporting on turnover ). The key creation is based on the principle of maximum selectivity (generally wiser to use the key "Document Type", "Document No." rather than "Document No.", "Document Type").