You can use SQL statements either interactively or within source code, to access and retrieve data that is stored the database. Data manipulation is performed through the following statements:
insert adds one or more new records into a table.
update modifies data in existing table records.
delete removes existing records from a table.
Before any data can be changed in Microsoft Dynamics AX, the data must first be selected for update by using a select statement. The select forUpdatecommand selects records exclusively for update.
The insert, update, and delete methods perform operations on only one record at a time. The array insert, insert_recordset, RecordInsertList, and update_recordset statements perform operations on multiple records at a time. However, the X++ record-set operations can fall back to record-by-record operations in a number of situations.
Speeding up SQL Operation :
The following constructs allow you to insert, update, or delete multiple records. Using these constructs reduces communication between the application and the database, and it increases performance.
Construct
|
Description
|
|---|
RecordSortedList
|
Allows you to insert multiple records in one database trip. Use the RecordSortedList construct when you want a subset of data from a particular table, and when you want it sorted in an order that does not currently exist as an index.
|
RecordInsertList
|
Allows you to insert multiple records in one database trip. Use the RecordInsertList construct when you do not need to sort the data.
|
insert_recordset
|
Allows you to copy multiple records from one or more tables directly into another table on a single database trip.
|
update_recordset
|
Allows you to update multiple rows in a table on a single database trip.
|
delete_from
|
Allows you to delete multiple records from the database on a single database trip.
|
In some situations, X++ record set operations can fall back to record-by-record
Insert_recordset :
insert_recordset copies data from one or more tables directly into one resulting destination table on a single server trip. It is faster than using an array insert. However, array inserts are more flexible if you want to manipulate the data before you insert it.
insert_recordset is a record set-based operator, which performs operations on multiple records at a time. However, it can fall back to record-by-record operations in a number of situations.
Syntax :
insert_recordset DestinationTable ( ListOfFields )
select ListOfFields1 from SourceTable [ where WhereClause ]
[ join ListOfFields2 from JoinedSourceTable [ where JoinedWhereClause ]]
The ListOfFields in the destination table must match the list of fields in the source tables. Data is transferred in the order that it appears in the list of fields. Fields in the destination table that are not present in the list of fields are assigned zero-values as in other places in X++. System fields, including RecId, are assigned transparently by the kernel in the destination table.
Examples :
Example 1
insert_recordset myTable (myNum, mySum)
select myNum, sum(myValue)
from anotherTable
group by myNum
where myNum <= 100;
The records, myNum and mySum are retrieved from the table anotherTable and inserted into the table myTable. The records are grouped according to myNum, and only the myNum records with a value less than or equal to 100 are included in the insertion.
Example 2 :
insert_recordset tableA (StringA, IntA)
select StringB, IntB from tableB
where tableB.IntB >= 9;
The fields StringB, and IntB (when it has a value that is greater than or equal to 9) are retrieved from the table tableB, and inserted in tableA into the fieldsStringA and IntB, respectively.
Example Comparing the Performance of Traditional Inserts and Insert_recordset Inserts
The following example shows the traditional way of copying data.
ttsBegin;
while select * from sqlDictionary
where sqlDictionary.tabId > 0
{
bufferDictionary.tabId = sqlDictionary.tabId;
bufferDictionary.fieldId = sqlDictionary.fieldId;
bufferDictionary.array = sqlDictionary.array;
bufferDictionary.name = sqlDictionary.name;
bufferDictionary.sqlName = sqlDictionary.sqlName;
bufferDictionary.fieldType = sqlDictionary.fieldType;
bufferDictionary.strSize = sqlDictionary.strSize;
bufferDictionary.shadow = sqlDictionary.shadow;
bufferDictionary.rightJustify = sqlDictionary.rightJustify;
bufferDictionary.nullable = sqlDictionary.nullable;
bufferDictionary.flags = sqlDictionary.flags;
bufferDictionary.insert();
countTables++;
operationProgress.setCount(countTables);
}
ttsCommit;
The following is the same example but uses insert_recordset.
void copySQLDictionary2DictionaryLine()
{
SqlDictionary sqlDictionary;
;
ttsBegin;
insert_recordset bufferDictionary(tabId, fieldId, array,
name, sqlName, fieldType, strSize, shadow,
rightJustify, nullable, flags)
select tabId, fieldId, array, name, sqlName, fieldType,
strSize, shadow, rightJustify, nullable, flags
from sqlDictionary
where sqlDictionary.tabId > 0;
ttsCommit;
}
Performance comparison:
When copying 14,311 rows, the number of trips to the SQL Backend has been reduced from 14,213 to 6. The SQL Backend time used has been reduced from 174 seconds to 10 second
Update Record_Set:
The update_recordset construction allows you to update multiple rows in a single trip to the server. This allows certain tasks to be significantly speeded up by utilizing the power of the SQL backend.
update_recordset is similar to delete_from in X++ and to UPDATE SET in SQL. It works on the database server-side on an SQL-style record set rather than retrieving each record separately by fetching, changing, and updating.
If the update() method is overridden, the implementation falls back to a classic looping construction, updating records one by one just as delete from does for deletions. This also means that the construction works on temporary tables, and entire-table-cached tables by using the looping construction.
Example 1
update_recordset myTable
setting field1 = myTable.field1 * 1.10;
This example updates the table myTable and increments the value in field1 by ten percent in all records in the table.
Example 2
update_recordset myTable
setting field1 = 1,
field2 = fieldX + fieldY
where field1 == 0;
This example updates the table myTable in all records where field1 has the value 0. field1 is assigned the new value 1; field2 is assigned the value of the sum of fieldX and fieldY.
This example updates multiple fields at once and is limited by the where clause.
Example 3 :
update_recordset myTable
setting field1 = 1, field2 = 'JD',
field3 = myTable.fieldN + 1000
where field1 == 0;
This example updates three fields in the table myTable. field1 is assigned the value 1, field2 is assigned the value JD, and field3 is assigned the value infieldN plus 1000.
The syntax allows for multiple, comma-separated fields having assigned values and expressions in each of these. Standard where clause
Delete_From :
The delete_from statement enables you to delete multiple records from the database at the same time. This means that there is only one call required to the database from the Microsoft Dynamics AX client. as opposed to the multiple calls required if you use the delete statement.
This reduction in the number of calls required speeds up system performance. It also enables you to specify multiple deleted items in a clearer, simpler manner.
In situations where the delete method is overridden, the system transparently emulates the situation of selecting and deleting one record at a time so that the overridden delete method is executed.
Example 1 :
MyTable myTable;
;
delete_from myTable
where myTable.AmountMST <='1000'
Example 2 :
ttsBegin;
while select forUpdate myTable
where myTable.AmountMST <='1000';
myTable.delete();
ttsCommit;
Example 1 deletes all records from the myTable table that have an AmountMSTvalue less than or equal to 1000. All records that satisfy this criterion are deleted from the database in a single action.
Example 2 deletes all records from the myTable table that have an AmountMSTvalue less than or equal to 1000. All records that satisfy this criterion are deleted from the database one at a time.
Maintain Fast SQL Operations :
There are a number of situations in which X++ record set operations can fall back to record-by-record operations.
The following table identifies these situations.
|
DELETE
_FROM
|
UPDATE
_RECORDSET
|
INSERT
_RECORDSET
|
ARRAY_INSERT,
RecordInsertList,
RecordSortedList
|
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
|
Overloaded method
|
Yes
|
Yes
|
Yes
|
Yes
|
skipDataMethods
|
Alerts set up for table
|
Yes
|
Yes
|
Yes
|
No
|
skipEvents
|
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();
}
