What “Avg Lines Posted Per Second” is considered normal when posting General Journal lines?
I know this is a repeat. But I’ve yet to see anyone flexing with “I got 1 million lines to post in 10 minutes by doing…”
My general observation: 10000 lines take 1h 15m on average 2.2 lines per second.
Does this pass the smell test?
Additional Comments / Areas of Concern:
Have run profiler: No one item stands out.
The Number of Lines in the batch has an effect on timing. Found a white paper from 2008 NAV - that appears to point to splitting batches in an effort to add more workers. But, I’m after Avg Lines per second in a single batch.
Sandbox environment. I’m making the assumption Production gives me a bit of a boost. But not enough to force testing in production.
My G/L Entry table has 5 custom fields on it that are not flowfields. There is a custom event to move custom fields on the general journal to GL Entry. Not really showing up as a bottleneck in the profiler.
My test data is not random. Posting 10000 identical lines to one GL Account and a single balancing line in the same account. Thus, 10000 credit lines @ $10 in one GL Account and 1 debit in the account for the sum of the credits. Our team posts all the time with real data and it’s hours and hours of waiting for a batch to finish.
Both global dimensions are populated in the general journal.
Analysis Views exist but I did know to turn Update on Posting off Prior to running.
Have you tried using Post with Job Queue on GL Setup?
Your problem might not necessarily be hardware or performance related. It’s due to the number of lines you’re posting. The way the posting process works, and how NAV tends to work is to store all transactions in a temporary record. Once it reaches the end without a problem, that’s when it will commit the transaction. This allows you to undo everything if there was a problem during posting. By this token, the more lines there are, the more memories it will take and the slower it will become as it processes them. Monitor the memory and speed, and you’ll see what I mean. To circumvent this, either reduce the number of lines or, the riskier option is to commit more often, like for every 1,000 records. The reason I say risky is because, if something goes wrong before the process can finish, you’re potentially left with invalid entries that were successfully submitted up until the error occurred. If you rarely encounter posting errors, then the risk may be lower for you. You can always try this in the test environment first, to see if it helps.
While it is true that NAV uses temporary records for posting the GL, this has nothing to do with committing the transaction. Changes written directly to the physical tables may be committed or rolledback as the situation requires.
NAV uses the temporary records to determine if the new entries balance. If they do, those temorary entries still need to be written to the physical table before the transaction can be committed.
I have done this plenty of times in the past and saw significant improvements. I suggest you try this to learn more about how the transactions work, it’s not really NAV that works this way, it’s SQL Server. Until a commit command is executed, or the process is complete, batch or not, you will not see the changes in SQL Server unless you run a command such as SET ISOLATION LEVEL READ UNCOMMITTED. The speed of the the posting degrades based on the amount of records to post. When I commit often, the speed improved dramatically. The trick is to know when to commit, not just randomly. I normally commit for whole records, say you’re batch processing 100 orders. Commit for every 10th or 20th order. This way, if something goes wrong, at least the ones that have been committed are for a complete order. In the author’s case, since there are 10K lines, committing for every order is likely best, but I’m not sure if there will be much improvements there since there are still so many records in between.
You are mistaken by what NAV is using temporary records for, think of it as an extension of a table, and that also means having to lock the table because no one else can write to it while you’re temporarily extending it. That’s what leads to record lock issues during heavy posting and why we should post using a job queue. As a matter of fact, what I suggest is exactly what a job queue does. It breaks the posting down to individual jobs that can be completed as a whole (where I suggested a commit). When you commit, it means “write to the physical table” or apply the temporary extension to make it whole. Now the table is unlocked and new records can be added.
If you look at the FinishPosting procedure in Codeunit 12, you will see the temporary table is used exactly as I describe. I’m not disputing much of what you are saying about possible performance improvements. I’m only saying that temporary tables have nothing to do with the transaction commit\rollback process.
I’m not looking to get into a “who knows more than who” argument. Frankly, I have more important things for my time. I have worked with SQL Server for over 30 years and NAV for about 25 years. I fully understand how transactions work.
I see what’s happening now. You mistook a temporary table in SQL Server to be a temporary table in NAV. We’re not talking about the definition of a temporary table in NAV, we’re talking about SQL Server’s own temporary table when working with practically any process that can be rolled back.
Generally speaking, temporary tables in NAV should never insert anything into the database and we should never have to use a commit command against a temporary table.
I guess I got confused by your use of the term “temporary table” to describe the transaction commit\rollback process is SQL server. While that process doesn’t exactly use temporary tables, I can see how describing it that was does make sense.
For some clarity, the commit is not the write to the physical table on the disk. When a change is committed, it is saved in memory and written to the transaction log. A background process, known as a checkpoint, flushes the changes from memory to disk. If a server is inappropriately shutdown, it may lose any changes that had not been flushed from memory. When that server is restarted, it will recover those lost changes from the transaction log and flush them to the database files before making the database available. This is the roll-forward process.
There is no specific benchmark for the “Avg Lines Posted Per Second” when posting General Journal lines as it can vary depending on various factors like hardware specifications, network bandwidth, complexity of the entries, customizations in the solution, and more.
However, your observation of 10000 lines taking 1 hour and 15 minutes, which is an average of 2.2 lines per second, seems low. It is recommended to investigate the cause of the slow performance by analyzing the profiling results, checking for any locks or blocking issues, reviewing the system resource utilization, and optimizing the query execution plan.
Additionally, splitting batches into smaller batches and adding more workers can help in improving the performance. But, it is essential to perform thorough testing in a non-production environment before implementing any changes in the production environment.
It is also recommended to consider upgrading the hardware and software specifications of the system to improve the performance. Furthermore, turning off the “Update on Posting” option for Analysis Views before running can also help in improving the performance.
Yes, I should’ve clarified the the term “temporary table”, I was using it too loosely to describe the process of data not being written to disk.
However, your description of commit is very strange. I’m aware of transaction logs as well, but that’s mainly for backup and restores, and drastic events like how you described, but it has nothing to do with the process. Until you commit, everything that you did up to that point will be lost because nothing is written to the transaction log either. Just think of it this way, you’re in the middle of a batch operation, your server shuts down for some unknown reason, it starts back up, it then takes everything in the transaction logs like how you stated and writes it to the database. Will it be with or without a commit command?
On the other hand, this will fit your restart scenario better. You issue a commit command, SQL Server saves what you’ve been doing in memory to a transaction log, which is smaller and faster than writing to a 60GB database. Before the operation can be completed, the server was restarted. When it starts back up, it checks the transaction log to see if it was completed before the shutdown. If it wasn’t that’s when it will issue the roll-forward command to bring it to the last successful “commit” operation. In other words, commit means “flush the changes from memory to disk.” When things are stored in memory, I call it temporary hence the “temporary table” I was referring to.
Actually all CRUD operations are written immediately to memory and the transaction log. Even prior to commit. Both these operations are completed before the user is released form the process. That’s why the performance of the transaction log is an important factor. The commit simply marks those changes as committed, allowing the checkpoint process to flush them to disk.
The transaction log is about much more the backup/restore. it is an integral part of the database operation. A database without a transaction log is a read-only database.
That’s great to know. I never gave it much thought about the background process since it’s rarely something we need to be concerned about, although it’s good to be knowledgeable of. However, I still don’t think your explanation of transaction logs to be correct and I find your explanation of commit to be very strange. Nevertheless, I think this is getting off topic.
The author has questions about the performance. Performance-wise, I’ve seen this happen often with batch transactions and what I’ve suggested have always done the trick for me.
Yes, we did get a bit off-topic there. But it was a fun discussion.
While I don’t dismiss your idea that the commit could be the performance issue, I don’t rule out it could be something else. But right now, we are both just guessing. There is a simple way to find if your idea has merit in this situation. That is to benchmark the preview posting. Just need to grab the end time before the rollback starts.
Thanks for the info. We have been decreasing the size of the batches, which does help a bit. Not Ideal, but we are limited in what we can do to speed things up being in the Cloud.