Inventory Valuation Performance on SQL

Alex_Chow

Does anyone out there have a good way to speed up the Inventory Valuation report using SQL?

The problem with the report is that it goes through every item ledger entry in the system and finds the appropriate applied entries.

The current process takes wwwaaaayyy too long! ](*,)

Alex_Chow

Ok, after receiving some hints from Mark Brummel, the inventory valuation report is running a lot faster now after tuning.

One of the areas where major bottleneck happens is the the Item Application Entry table. I had to create indexes for the WHERE clause on the Item Application Entry table. This also boosted the performance when the Adjust Cost - Item Entries is ran.

I think the reason why it's not there in the first place was because of performance issues during INSERT or UPDATE in SQL.

BTW, I like to solve my own questions on this forum.

Alex_Chow

One more thing, in case you don't know, you can find the WHERE clause using the SQL Profiler.

You can find what index SQL is using by activating the Client Monitor.

FDickschat

Hi Alex,

could you let me know a little more about the changes you did to speed up cost adjustment?

You wrote that Item application was one of the major problems but that implies you did more modifications than just that.

Thanks for your help.

Best regards, Frank

Alex_Chow

Hi, creating the indexes on the Item Application Entry is what I did. It did speed up the report a bit, but still pretty slow.

I'm using the US version of the report by the way.