I don’t know about you but my impression is that Axapta has some problems with table locking. I have searched Internet about it, and have found the term “lock escalation”. Apparently, what happens is that Axapta tries to lock the row it is updating, then it doesn’t release that row, and proceeds to locking second row, and then locks the whole table. I have been to Moscow recently and talked with our partner about it, and they say it would be best that instead creating one big transaction, you create many transactions, where each transaction has only one line. Probably this post would not go into developer forum, but I have and X++ example. The problem is that our partner created a function for copying Sales Order to Invent Journal → Transfer, and it is little buggy, and sometimes it creates two lines with same LineNum. So they have sent me this job for fixing bad journal lines: TTSBegin; lineNum = 1; while select forupdate trans where trans.JournalId == '002613' { trans.LineNum = LineNum; LineNum++; trans.update(); } TTSCommit; This job took like more than one hour on Journal with little more than hundred lines. This is probably because of locking a lot of records? What do you think? I have created a job of mine, that performs real fast, because it locks just a single row at one time: WHILE SELECT COUNT(RecId), LineNum FROM trans GROUP BY LineNum where trans.JournalId == journalId { if (trans.RecId > 1) { SELECT MAXOF(LineNum) FROM ijt WHERE ijt.JournalId == journalId; lineNum = ijt.LineNum; TTSBegin; SELECT FORUPDATE FIRSTONLY ijt WHERE ijt.JournalId == journalId && ijt.LineNum == trans.LineNum; ijt.LineNum = lineNum + 1; ijt.update(); TTSCommit; } } I have talked with them about this problem again, and they say locking depends on amount of records you are selecting and amount of memory on server. We have 6 GB MS SQL Server and 2 GB AOS. We are generaly having problems with table locking, especialy when posting Purchase Orders. The problem is that we are not sure where is the root of this problem. Did someone have similiar experiences?
Have you tried using SQL Profiler to find out exactly what is hapening and why it is taking so long? Also, you mentioned that your SQL server had 6 GB of ram…are you running SQL Server Enterprise? Because if not SQL Server Standard can only use up to 2 GB of RAM…