Optimizing database trips for data retrieval

Question

For multiple data insertion we have an efficient way: RecordSortedList

RecordSortedList rsl;
MyTable          myTable;
;
rsl = new RecordSortedList(myTable.tableid);
rsl.sortOrder(fieldname2id(myTable.tableId,'RecId'));
myTable.field1 = 'Value1';
rsl.ins(myTable);
myTable.field1 = 'Value2';
rsl.ins(myTable);
rsl.insertDatabase();

Is the same possible for multiple records retrieval from db in one go? Something like

int i =1;
while(i<10000)
{
  //enter records from db into a buffer in db
  i++
}
//now bring the buffer from db in a single trip 
//and do the data manipulation in AX

My intention is to optimize the db trip to the least. Please Suggest.

Answer 1

The insertDatabase method as stated (use the RecordInsertList class instead of RecordSortedList, if you do not need the sorted order):

inserts multiple records on a single trip to the database.

However this is mostly from the programmers perspective. The operation from the SQL goes like this:

INSERT INTO MyTable ( Column1, Column2 ) 
VALUES ( Value1, Value2 ), 
       ( Value1, Value2 ), ...

There are limits to the number of records inserted this way, so the AX kernel may split the list to make several calls to the SQL server.

The other way from DB to AX is easy:

while select myTable where ...

Which is translated to SQL as:

SELECT T1.Column1, T1.Column2 FROM MyTable T1 WHERE...

This transports the data from the table to AX as efficient as possible.

You may choose to use a QueryRun object instead, but the call to SQL stays the same.

If you do simple updates on the table, consider using update_recordset as this may move the updates to the SQL server and eliminating the round-trip.