Posting is slow on NAV 2009 with SQL Option

kash387

Hi experts,

greetings....!!

One of my client is facing an issue and it is, Transfer Order Posting is very slow and duty to that slow process other users got the Locking issues.

The client was facing the Inventory Cost Adjustment isssue, we gave them a solution, it worked fine and the items are processed for Cost Adjustment too.

Now, after that the client is facing this kind of problem with performance of NAV/SQL. (As usual) They believe its because of our code or process for the cost adjustment issue. (I believe its not because of that.)

Before posting this, i have gone through Key part of ILE table and also read the Jorg Stryk's blog for SIFT/VSIFT optimization.
ILE contains 40 lines of keys (MaintainSqlIndex = True for each line).

So, please experts, guide me on this...!!!!

Thanks and Regards,

Kashyap

bbrown

Can you expand on what your "solution" was? Since the client started complaining about performance after your "solution" was implemented why would you think it had nothing to do with it? It may not be the whole problem though.

Performance issues are typically not a single thing. Many times they are several seemingly tiny issues that together result in unacceptable performance.

kash387

bbrown wrote:

Can you expand on what your "solution" was? Since the client started complaining about performance after your "solution" was implemented why would you think it had nothing to do with it? It may not be the whole problem though.

You are right bbrown, that's what I thought first.
Well, my solution was based on ILE table and IAE table. And it's a process that had been finished. After that they started doing transaction, and the transaction is taking lot of time.

Now, ILE contains 5 million entries, same the IAE does, for all the keys "MaintainSQLIndex" is TRUE though the Sum Index Field is blank.

That's why I say it's not because of the solution given by me..!! It's a locking issue, and my process is finished so it had released the locks also.

Still, please guide me if I am on wrong way, or please guide me towards the solution.

Thanks,

Kashyap

bbrown

I don't really have enough information to guide you to a solution. At this point I'd just be guessing.

"MaintainSQLIndex" has nothing to do with SumIndexField. MaintainSIFTIndex is what controls that.

How large are the transfer order?

Are you using "Automatic Adjust Cost"? If so, turn it off.

Anything else in the system slow?

Have you run the process with SQL Profiler or the NAV Client Monitor to identify the slowest calls?