Maintain Fast SQL Operations

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

There are situations where X++ SQL record set operations can be converted to slower record-by-record operations.

The following table identifies these situations.

	DELETE_FROM	UPDATE_RECORDSET	INSERT_RECORDSET	ARRAY_INSERT	Use ... to override
Non-SQL tables	Yes	Yes	Yes	Yes	Not applicable
Delete actions	Yes	No	No	No	skipDeleteActions
Database log enabled	Yes	Yes	Yes	No	skipDatabaseLog
Overridden method	Yes	Yes	Yes	Yes	skipDataMethods
Alerts set up for table	Yes	Yes	Yes	No	skipEvents
ValidTimeStateFieldType property not equal to None on a table	Yes	Yes	Yes	Yes	Not applicable

 

You may explicitly skip or ignore one or more things that would adversely impact performance by using the items shown in the far right column. If for some reason one of the previously mentioned SQL operations is downgraded to a record-by-record operation, all of the skip… settings are also ignored. For example, the insert method on myTable is executed in the following example—even though it is explicitly stated that this method should be skipped if myTable has a container or memo field defined.

    public void tutorialRecordInsertList()
    {
        MyTable myTable;
        RecordInsertList insertList = new RecordInsertList(
            myTable.TableId, 
            True);
        int i;
        
        for ( i = 1; i <=  100; i++ )
        {
            myTable.value = i;
            insertList.add(myTable);
        }
        insertList.insertDatabase();
    }

 

For more information about delete actions, see Maintaining Data Integrity.

See also

Speeding Up SQL Operations

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.