delete records in axapta damn slow

Question

hi there,

i've got a form in ax 2009, showing filtered records of a table ( about 5.000.000 records total, about 1000 shown filtered ). selecting a couple of those records in the form and deleting them via form-control ( alt+f9 ) is damn slow. one record is deleted immediately, selecting about 20 takes several minutes! there is only one deleteAction on the table - any idea what could thwart the operation?

thanks in advance!

edit:

the regarding table has two indices. both don't allow duplicates. first one is an index on an integer field. second one is a combined one of three fields.

createRedIdIndex is not activated.

the filter makes use of one column ( employeeID ) in a queryBuildRange.

deleteAction: another table (B) references the id ( indexed ) of the mentioned table (A). A has a deleteAction on B. setting is "cascade"

The two tables are related via id-field.

The relations can be resolved by an index.

And it's only an amount of about 20 records i want to delete - so i don't go in line with the idea, that the "to-delete-data-amount" is too big!

Answer 1

Do diagnose database performance issues in AX, enable SQL tracing in Tools\Setup in the SQL tab page.

Answer 2

Use the code profiler to see where the time is used.

Answer 3

Also have a look on this: http://blogs.msdn.com/b/emeadaxsupport/archive/2010/07/12/forms-with-a-high-number-of-records-take-a-significant-time-to-show.aspx

Consider adding

grid.autoSizeColumns(false);

as suggested in the article.

Answer 4

Deleting/manipulating via forms is always slow when you have that many records in the form, I've once run a server out of memory trying to copy two dozen CustTable records to Excel (AX3, copy+paste) :)

Does the table B have indexes? Does it have a lots of records, and a lot of records per one table A record, like CustTable - CustTrans?

Does this slowness also happen if you try deleting via the table browser?