Huge Cust. Ledger Entry table on SQL

wester

Hi there

I'm running af 4.02 SQL runtime (3.60 base), where I'm experiencing a rather big delay when posting payments in a standard journal. The problem is table 21 Cust. Ledger Entry which consists of more than 9 million records. The posting routine is complete standard using the standard defined keys, but it's extremely slow during this process. Trying to reduce this table to about 500000 records increases performance to an acceptable level.

The hardware is completely up-to-date and really fast and the Navision code i standard, so I'm pretty sure it's table 21 which creates the problem.

Anyone have an idea of what I can do to increase performance posting payments?

Thanks :-)

/R

Saint-Sage

If the server can pull the full index into memory, then it is able to do a binary search on the index to find each piece of data. If for some reason it is forced to perform a linear search due to memory constraints, then it will take an enormous amount of time compared to the binary search. This may be what is happening, as if you remove half the records it speeds your performance up.

I am not sure if you could create a new table to archive older records in that table to keep the number below 5 million, otherwise you may need to increase RAM on the server. Depending on what version of SQL server and what configuration you have, you may only be using 2GB of RAM.

This is from a SQL Performance Guide I have :

--
If you have 4GB RAM on the machine, but 2GB only is used by SQL Server, then you must use Windows Advanced Server and enable 3GB switch so that SQL server will understand it is looking for more than 3GB of RAM.
--

Marije_Brummel

Posting payments in a not-tuned SQL NAV database is very slow, but you can change it.

Try to first remove the overhead in the ledger entry tables, there are a lot of unnesesaiy indexes and sift tables.

Next step is to create better indexes depending on how you use the system.

Indextuning is different per customer, example: last week I tuned Get Purchase Shimpent Lines without customisation, this week I tried the same trick at a different customer with a slightly modified database and had to change the index.

Waldo

True and confirmed ... .

It's very difficult to share information on how to deal with situations like this, because it's some kind "reasoning" and knowledge you should posess from both NAV and SQL Server.

What I would do (small addition to Mark's post):
- Remove unnecessary indexes and sift buckets (like Mark said)
- use SQL profiler to identify the "hot" statements in the process. It shows you how the user is using the functionality (filters, sorting).
- create/enable specific indexes for this process.

Yes, this way, it's possible you make other processes slower, so beware what you're doing.

kriki

Waldo wrote:

- Remove unnecessary indexes and sift buckets (like Mark said)

Dates create 3(!) SIFT-buckets: per day, per month, per year.
I think it is not needed to maintain the ones on month and year.
Because most of the time, you don't filter on exact 1 month or year. And if you do, Navision sends a SELECT SUM on the SIFT bucket of day and not on the real table. So this is acceptable.

Waldo

kriki wrote:

Waldo wrote:

- Remove unnecessary indexes and sift buckets (like Mark said)

Dates create 3(!) SIFT-buckets: per day, per month, per year.
I think it is not needed to maintain the ones on month and year.
Because most of the time, you don't filter on exact 1 month or year. And if you do, Navision sends a SELECT SUM on the SIFT bucket of day and not on the real table. So this is acceptable.

Indeed ... I allways make sure the lowest level is selected .. so you have all the detail maintained. In case of dates, it's the "day"-level.

Also, when e.g. Variant Code is not used, I don't deselect Variant Code, but the level above. I do this, because when NAV searches for a bucket, and the bucket is disabled, then it's going to the NEXT bucket, not the previous one.