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.

i do know a bit... im just wandering that anyway it will take 500+ trips to store data in the map(class) at least. Can that be done in any other way — piku, Jan 30 '14 at 18:51
Not if you use a single SELECT statement to retrieve the data for the map. — Robert Harvey, Jan 30 '14 at 18:59
Could you please revisit your question or mark an answer as correct. — Jan B. Kjeldsen, Feb 03 '14 at 21:05

score 2 · Answer 1 · answered Jan 30 '14 at 23:08

Yes, it's called RecordLinkList - http://msdn.microsoft.com/en-us/library/aa643250(v=ax.50).aspx

A recordLinkList is a double linked list that can hold records of different types at the same time. It is not keyed or sorted.

The recordLinkList is particularly useful for passing records from different tables as a parameter instead of retrieving the same records again.

There is no limit to the size of a recordSortedList; it is the responsibility of the programmer to control its size and, therefore, memory consumption.

You can also add different types of records.

static void RecordLinkList(Args _args)
{
    RecordLinkList      rll = new RecordLinkList();
    SalesTable          salesTable;
    CustTable           custTable;
    InventTrans         inventTrans;
    Address             address;
    boolean             iterate;
    ;

    select firstonly salesTable;
    select firstonly custTable;
    select firstonly inventTrans;
    select firstonly address;

    rll.ins(salesTable);
    rll.ins(custTable);
    rll.ins(inventTrans);
    rll.ins(address);


    iterate = rll.first();

    while (iterate)
    {
        switch (rll.fileId()) // FileId == TableId
        {
            case tablenum(SalesTable):
                salesTable = rll.peek();
                info(strfmt("SalesTable");
                break;

            case tablenum(CustTable):
                custTable = rll.peek();
                info("CustTable");
                break;

            case tablenum(InventTrans):
                inventTrans = rll.peek();
                info("InventTrans");
                break;

            default:
                error(strfmt("Table %1 (%2) not expected", tableid2name(rll.fileId()), rll.fileId()));
        }

        iterate = rll.next();
    }

    info("Done");



}

This will find the records inserted manually record by record. It may not be what @piku expected. — Jan B. Kjeldsen, Jan 31 '14 at 07:40
I suppose I don't really understand his question. I saw he wanted to insert records into a buffer and in one of his comments he was using the Map class. And he found RecordSortedList, so I figured RecordLinkList might be along the same lines of what he's looking for. — Alex Kwitny, Jan 31 '14 at 16:25
I have added RecordLinkList in my code which has increased the performance, thank you for that. But my question as @Jan said is about an efficient way to retrive data from database just like we can use insertDatabase() for insertion, update_recordset for updation etc. — piku, Feb 04 '14 at 10:22

score 1 · Accepted Answer · edited May 23 '17 at 10:31

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.

edited May 23 '17 at 10:31

Community

1
1

answered Jan 31 '14 at 07:56

Jan B. Kjeldsen

17,817
5
32
50

thanks for you answer. I think we have efficent ways to limit the Db trip for update, insert and delete operation and not for data retrieval? – piku Feb 04 '14 at 10:25
You can reduce the time by selecting only the fields you need. Otherwise a `select` is as fast as it can be. – Jan B. Kjeldsen Feb 06 '14 at 07:56
I have selected the required columns only and returnd the selected records in a RecordLinkList. The performace has shown a difference from 30+ mins to 4-5mins. Another reason for the delay in my case is I was drawing lines for each cells(excel report), which I have removed later. – piku Feb 07 '14 at 05:01
Also consider `insert_recordset` if you have a simple copy from source to target. This should give an 10 fold speed increase. – Jan B. Kjeldsen Feb 07 '14 at 06:56

Optimizing database trips for data retrieval

2 Answers2