Inventory valuation report in SQLrunning very slow

Hi We have a customer who is on Navision 3.60 (AU) with SQL database. They are having performance issues when running the Inventory valuation report. This almost takes 30-40 mins to run the report and it even takes the same bit of time when no one is logged into Navision. I took a backup of the database and restored it locally. And it just took less than 5 mins to complete generation of the report Have any of you there experienced similar issues with this report, and is there a resolution to overcome this problem? Please advise… Thanks Ram

I’m not quite sure if especially this one has been solved, but Microsoft has been solving some posting issues with larger postings on SQL server regarding speed in 3.70.B. But I’m quite sure this does not really help ?[V]

Hi! What kind of backup did you restore? If you have restored a NAVISION backup (FBK) and not a SQL Backup, than the reason could be, that the indexes in the original db are insufficient: When restoring a FBK, the indexes are created new - so they are optimized and the report runs faster. If that’s the reason, you should take a look at the indexes in the SQL db and re-organize them by restoring the set fillfactor (maybe using a Maintenace Plan?) P.S.: I suppose you restored it to local SQL database and not to a FDB! Regards,

quote:

What kind of backup did you restore? If you have restored a NAVISION backup (FBK) and not a SQL Backup, than the reason could be, that the indexes in the original db are insufficient: When restoring a FBK, the indexes are created new - so they are optimized and the report runs faster.

Sorry, I have to correct myself a little (when I made the previous posting I was in a meeting, so obviously a little bit un-concentrated [:I]). Actually, it does not matter if you restore a Navsison Backup (FBK) or a SQL Backup (BAK): When restoring, the indexes are re-created. Regards,

What is his report run time for other reports? Are they also SLOW? I have a user that is on the other end of a SLOW network link between buildings. His performance running Navision and Jet reports was so bad that someone in this buiding used to run his reports for him then email them to him. We finally had to create a duplicate Navision server in his building, for him to run his reports from. I have a script to refresh his db once a week. Gary

Hi Joerg, Thanks for your response. I restored an (FBK) backup. Restored the backup into a .FDB file (local database). And also into a SQL database. The report ran much quicker in the local database which is expected as it is just a local version. But the backup in restored in the SQL server still runs the report very slow. But i did not do the indexing to the SQL db. So you are sure by indexing it should defintely increase the performance of the report. Best Regards, Ram

Hi! First, as a basic rule: The behaviour of any NAVISION application (or object) is allways (completely) different in SQL than in FDB!!! If you’ve restored a FBK into a SQL db than the indexes are rebuilt, too. So it depends on your “overall” SQL setup (server config, db config, files, filegroups, indexes, statistics, …). If you have a chance to do this, you should monitor the report with the “MBS Performance Troubleshooting Tools” (refer to the “Performance Troubleshooting Guide”) or the SQL Profiler. I’m afraid, there can be MANY reasons that give bad performance. (To check your overall SQL server performance you could use this doc: http://www.mibuso.com/dl.asp?FileID=356&Type=file) Just guessing: As we are talking about “Inventory Valuation”, the troublemakers could be insufficient indexes in “Item Ledger Entry” and “Value Entry”. Also insufficient SQL statistics decrease performance, especially when MANY records of one table have to be processed … Regards,

send a fob to me, I will check it for you

Hi Joerg Thanks for your suggestions. I will give it a go and monitor the performance based on your suggested guidelines. I will keep you posted on the results. Hi jatuphot, This is the inventory valuation report (standard report) with no modifications. You should be find the report in the World wide version.

Hi Joerg Thanks for your suggestions. I will give it a go and monitor the performance based on your suggested guidelines. I will keep you posted on the results. Hi jatuphot, This is the inventory valuation report (standard report) with no modifications. You should be find the report in the World wide version. Thanks

Hi Ram, I fixed a performance problem for one of our SQL clients who does a lot of revaluation and cost adjustments. It looks like a Navision bug but I can’t believe I’m the first person to find it, so please anyone correct me if I’m wrong. The Cost Amount flowfields on the Item Ledger Entry table refer to the Value Entry table where the Item Ledger Entry No matches. Yet in the Value Entry table the MaintainSIFTIndex property is not set on the relevant keys, meaning that Navision can’t use an index to calculate the cost flowfields and is therefore very slow. I set the property for the second key on the Value Entry table and performance has increased considerably. Your inventory valuation report will almost certainly be using the same fields, maybe you could try this? But beware - when you change the object it initially takes a long time to update the index. Oh and if it’s a problem in 3.7 then the code is the same in version 4. Regards, Dan.

Hi Dan, Wonderful, looks like a very simple solution. I will give it a try and check whether i can see any improvement. Thanks for your suggestion. Best Regards, Ram

Dan, Did you choose to maintain all SIFT levels?

I did choose to maintain all Sift levels, but only because I was in a hurry. I’ve since changed it to only maintain the first one and it’s much more efficient on database space without affecting performance. I’d recommend this unless the client is big on expected costs, in which case you may want to look at other levels as well. cheers, Dan.

And here’s the official response from Microsoft: The MaintainSIFTIndex property determines whether SIFT structures should be created or dropped in SQL Server to support the corresponding SumIndexFields for the Navision key. By design, the MaintainSIFTIndex of the “Item Ledger Entry No.,Expected Cost,Document No.,Partial Revaluation,Entry Type,Variance Type,Adjustment” key was dropped in the standard database . And the Development Team in Denmark makes it flexible for partners to choose whether to setup this property, on the Value Entry table, or not depending on the requirements of their customers. There is a need to evaluate the pros and cons of activating or maintaining the SIFT index of the key in question. I recommend that you try to evaluate the performance on browsing Item Ledger Entries and Adjust Cost routines if the MaintainSIFTIndex is enabled or not in this key. SumIndexFields are created in Navision to support, for example, FlowField calculations and other fast summing operations. SQL Server can sum numeric data by scanning the table. If the SIFT structures exist for the SumIndexFields, summing the fields will be faster, especially for large sets of records, but modifications to the table will be slower since the SIFT structures must also be maintained. In situations where SumIndexFields must be created on a key to allow FlowField calculations, but the calculations are performed infrequently or on small sets of data, you can disable this property to prevent slow modifications to the table. Good on them for thinking of us and giving us the flexibility - but where the #%&* is this documented? It’s not as if it’s obvious from looking into the code, you have to have a hunch about what might be happening, then look at the keys and then unhide the appropriate fields. I wonder if they’ve done the same thing in other areas? Dan.