SQL Database Performance - Key Question

Toddy_Boy

Dynamics 2013 SQL 2008 R2

Looking through some slow running queries I've noticed most of them are SELECT SUM() which I believe is equivalent to CALCFIELDS. One of the longest running queries is in CU 7301 InsertWhseEntry which seems to be this statement - WhseEntry2.SETCURRENTKEY("Bin Code","Location Code"); (code is still present in 2016 without the index) then a WhseEntry2.CALCSUMS("Qty. (Base)");

There is a key in standard NAV which includes "Item No." (Bin Code,Location Code,Item No.) however not one that is only Bin and Location

If you run the resultant query through the "Display Estimated Execution Plan" the advice is to create the missing index Bin, Location. On doing this performance seem to noticeably improve.

I've migrated proprietary to SQL in the past and the SQL version performs better when declaring keys with all the fields.

Can anyone throw some light onto why the Bin Code,Location Code,Item No. key would be ignored or have I looked past something?

(There is an overnight job that rebuilds indexes on the Warehouse Entry table.)

Regards

Steve

JP_Bagdonas

what i mean by "many" is that if you write one function where you call the SETCURRENTKEY multiple times for the same record it will slow down your performance. since you seems worried about it i gave you this hint.

back to you question... if you have a key for "No.","Line No." and SumIndexFields "Amount", it means that it will sum for you the values quickly when you are filtering on the key values. if you setup a odd filter that takes times to be calculated it will return a slow result. therefore i suggested you to create a new key that would improve your sum speed

hope it helps

JP_Bagdonas

try to add keys to assist you SUM, then it will run much faster

make sure you dont use many SETCURRENTKEY or SELECTLATESTVERSION or COMMITS, as they slow down performance

Toddy_Boy

Why would NOT using SETCURRENTKEY help? Is this in general or just when using CALCFIELDS?

JP_Bagdonas

what i mean by "many" is that if you write one function where you call the SETCURRENTKEY multiple times for the same record it will slow down your performance. since you seems worried about it i gave you this hint.

back to you question... if you have a key for "No.","Line No." and SumIndexFields "Amount", it means that it will sum for you the values quickly when you are filtering on the key values. if you setup a odd filter that takes times to be calculated it will return a slow result. therefore i suggested you to create a new key that would improve your sum speed

hope it helps