Really slow G/L Invt. Recon. screen

Dave_Cintron

This clever form (5845) released in 5.0 is taking over 30 minutes, just to display the amounts, on large SQL databases. Has anyone found a way to speed this up? We tried optimizing the Value Entry table - no change.

garak

does it takes 30 only on open form or if you press the Calculate Button?
Did you check with the SQL Profiler, why it takes over 30 minutes (Do you know how do you can do this)?
What for an optimize are you trieing for the Value Entries?

Regards

Dave_Cintron

I am not trying to fix this, I just want to find out if someone else has. Microsoft should have made this more efficient. It does no good to have a form that takes an hour to display the answer, any more than I can feed myself off a chicken that lays one egg a day.

Alex_Chow

Dave Cintron wrote:

I am not trying to fix this, I just want to find out if someone else has. Microsoft should have made this more efficient. It does no good to have a form that takes an hour to display the answer, any more than I can feed myself off a chicken that lays one egg a day.

That's like asking if we can speed up the Inventory Valuation or the Inventory to G/L Reconcile report. It's basically using similar logic.

stryk

Dave Cintron wrote:

I am not trying to fix this, I just want to find out if someone else has. Microsoft should have made this more efficient. It does no good to have a form that takes an hour to display the answer, any more than I can feed myself off a chicken that lays one egg a day.

Yes, MS maybe should ... but reality is different ... else I would not do the job I do ... 8)

If you want to get this fixed you have do do it yourself or with some "external" help; waiting for MS is pointless.

With NAV 5.0 (without SP1) you firstly need to look into SIFT optimization (http://dynamicsuser.net/blogs/stryk/archive/2010/05/29/optimizing-sift-and-vsift.aspx).
Then - as garak already mentioned - the SQL Profiler will unveil the full truth about that issue ...

Dave_Cintron

I don't think the client is going to want to pay for 20 hours of SQL Profiling and SIFT optimization study. If the GLEntry.CONSISTENT statement can work in an instant, I suggest we use the CalcSums and add a ValueEntry.WHATISMYINVENTORYWORTH statement. Then we can code IF GLEntry.CONSISTENT <> ValueEntry.WHATISMYINVENTORYWORTH THEN CurrReport.CREATESUPPORTINCIDENT.

David_Singleton

For years users and partners kept requesting from Navision a report that could retrospectively give inventory valuation as if the report had been run 3 months ago. Always Navision said "no it would be too slow" but finally they gave in and gave the report and built the logic. Of course the first response was "But this is too slow".

What I never understood is why they didn't also keep the old logic, so that clients could chose between Fast or back date able?

The problem is that inventory costing has kept evolving adding more features and more options and getting slower and slower. Its no longer possible to keep doing this and its time to throw it out and start again.

As to the argument of Microsoft making it faster vs the partner: I have modified inventory for many clients, and made it a lot faster as they needed. BUT ... in each case the requirements were different, so I was able to remove many un-needed features thus making it faster. Of course the next customer needs something different, so you can't just use what you did the last time, you need to do it again. The advantage though is once you have done it a few times, you know what makes sense and you can then do the next project faster.