Hello, Does somebody know why deleting large amounts of records (>1.000.000) with the DELETEALL(false) command takes so much time (deleting 1.000.000 rec. takes aprox. 12-14 hours). I’am NOT using setfilter or setrange. I’am using Navision Finacials 2.6D with MS SQLSERVER 2000 Code example: MyTableRec.RESET; MyTableRec.DELETEALL(false); Edited by - piebee on 2001 Nov 13 14:16:57
Your question is incomplete, because your server hardware configuration is required for the answer. Anyway, the poor efficiency is a normal thing when you are using a table filter based on non-indexed fields. Table filters based on the indexed fields work much faster. Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD ypokusaev@yahoo.com +7(095)987-8080
Although the DELETEALL and its cousin, MODIFYALL, are very easy to use, they are very slow when large numbers of records are involved. In such cases, if this is to be a repetative process, I usually write a simple code loop to delete a few hundred records at a time. As mentioned previously, make sure you are using an efficient key. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner
why is the DELETEALL slower? could it be that it rewrites all the data to a temporary storage to enable a roll-back in case the deletion is cancelled?
I think you will find that this is related SQL, or at least the way Navision is implemented in SQL. Of course if you meant DELETEALL(TRUE); then that could be another story. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________
I’ve viewed SQL commands passed to MS SQL Server during the DELETEALL execution. In case of no filter activated the command is: C/SIDE: DELETEALL(FALSE); Transact SQL: DELETE FROM “MyDB”.“dbo”.“MyTable” It executes very fast. In case of any filter defined the command is: C/SIDE: RESET; SETRANGE(code , ‘10000000’, ‘90000000’); DELETEALL(FALSE); Transact SQL: sp_executesql N’DELETE FROM “MyDB”.“dbo”." MyTable" WHERE ((“code”>=@P1 AND “code”<=@P2))’, N’@P1 varchar(10),@P2 varchar(10)’, ‘10000000’, ‘90000000’ what equivalent to DELETE FROM “MyDB”.“dbo”." MyTable" WHERE ((“code”>=‘10000000’ AND “code”<=‘90000000’)) In this case the “WHERE *” clause only affects the SQL execution efficiency. So, you have to use efficient key to avoid DELETEALL command execution slow down. Regards, Yuri Pokusaev IBS, Senior Consultant NCPS, NCSD ypokusaev@yahoo.com +7(095)987-8080
→ David S. On SQL this is not the case - the DELETEALL is more efficient than a DELETE in a loop since it is implemented with a single DELETE statement. The loop implementation sends multiple separate DELETEs within a cursor == not recommended. Yuri is correct in his observations. When using a DELETEALL with a range then the usual rules apply for any navigation on a field value - a good index is required. There are some cases with SQL Server 2000 where DELETE statements use a bad execution plan for a perfectly valid WHERE clause and a good index present, resulting in a clustered index scan. Try updating statistics (using FULLSCAN) on the table in question.