web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to work with large...
Finance | Project Operations, Human Resources, ...
Unanswered

How to work with large data set with minimum db sql server calls

(0) ShareShare
ReportReport
Posted on by Mav

Hi experts,

I have a table myTable which has a column name Id. The Id is not unique & hence each Id can have multiple related records.

My requirement is to fetch 1 data set of say 100 unique ID from myTable & then parse these 100 unique ID to get its all related records from myTable

Something like

Step 1 Get 100 unique Id from mYTable

Step 2 Now get all records related to these 100 id's from myTable

STep 3 Filter what you got in step 2 via " type "column

Step 4 Use the step 3 filter data to handle some business logic for "type" ( i will take care of this part).

Step5 Now use the step 3 filter data set & filter it more with"sub type " column

Step 6 use the above filter data to handle some business logic for "sub type " ( i will take care of this part).

Currently my solution is working but I have to use multiple while selects on several buffers of myTable. hence was looking for a solution using which i can store the data i nee in Step2 on runtime basis like in temptable or collection & then keep using that data for all my data needs in subsequent steps.

With temptable approach , challenge i m facing is that the moment i change my temptable buffer name i loose all data stored in my initial temptable buffer, so is there  away i can retain the temptable intial buffer data in subsquence bufferes using tablebuffer.data() /buf 2 buf . If not can you suggest alternative ways of how I can store the data collected in step 2 during runtime like how do i get it in list or container if not at all temptables can be used.

The end objective here is to minmize the db sql trips which is best done by storing the data generated step 2 during run time.

Thanks

Mav.

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,976 Most Valuable Professional on at

    It seems that you don't know how to use temporary tables.

    It's not clear what exactly you're doing, because variable names can't be changed at runtime. It would help if you started providing more details about your problems. It's in your best interest...

    Let me give me a brief introduction to temporary tables:

    You must realize that you can have multiple instances of data sets of each temporary table. It's the same as with classes - you can create many objects and populate them with different data. And as with objects, it's important to which object your variable is pointing to.

    For example, this code creates two completely independent temporary buffers:

    MyTable a;
MyTable b;

    If you insert data to a, you won't find them in b, because that's a different data set.

    But... you can assign a to b and then both will point to the same data, in the same way as variables for a class would point to the same object:

    b = a;

    Now you can insert data using any of the variables, because they point to exactly the same thing.

    There is also an option to tell a buffer to point to a data set of another buffer, which is useful if you can't assign to it (typically with form data sources). The solution depends on the type of temporary table: you'll use setTmpData() with InMemory tables and linkPhysicalTableInstance() with TempDB tables.

    Now when you know the basics, you can play with code and look for more details on the internet

  • Mav Profile Picture
    Mav on at

    Here is the code & the code within 2nd while select statement never executes.

    Can you please suggest a fix.

            
    mymethod()
    {
            MyTable myTable, myTableNtExstBuf;
            MyTmpTable myTmpTable1, myTmpTable2;

            insert_recordset myTmpTable1 (Id)                                         
                select firstonly100 Id from myTable
                group by Id;
    
    
                insert_recordset myTmpTable2 (Id,F1,F2,F3,refrecid)
                    select firstonly100  Id,F1,F2,F3, RecId from myTable
                   where    myTable.Id ==   myTmpTable1.Id;
    
                while select myTmpTable1
             
                {
                   this.myCustomLogic(myTmpTable1);                // CODE WORKS FINE TILL HERE, WORKS AS EXPECTED can see customlogic being called with correct /expected values.

                    while select  Id,F1,F2,F3, RecId f from myTmpTable2   // Execution NEVER enters this 2nd while select statement
                            order by F2 desc
                            where  myTmpTable2.Id == myTmpTable1.Id
                        notexists  join myTableNtExstBuf
                        where myTableNtExstBuf.f3 == myTmpTable2.f2
                    {
                           this.myCustomLogic(myTmpTable2);
                    }

                          
        }

    Thanks

    Mav

  • Martin Dráb Profile Picture
    Martin Dráb 237,976 Most Valuable Professional on at

    First of all, let me format your code to make it easier to read:

    MyTable myTable, myTableNtExstBuf;
MyTmpTable myTmpTable1, myTmpTable2;

insert_recordset myTmpTable1 (Id)                                         
	select firstonly100 Id from myTable
	group by Id;

insert_recordset myTmpTable2 (Id,F1,F2,F3,RefRecId)
	select firstonly100 Id,F1,F2,F3, RecId from myTable
	where myTable.Id == myTmpTable1.Id;

while select myTmpTable1
{
	this.myCustomLogic(myTmpTable1); // CODE WORKS FINE TILL HERE, WORKS AS EXPECTED can see customlogic being called with correct /expected values.

	while select Id,F1,F2,F3, RecId f from myTmpTable2 // Execution NEVER enters this 2nd while select statement
		order by F2 desc
		where myTmpTable2.Id == myTmpTable1.Id
		
		notexists join myTableNtExstBuf
			where myTableNtExstBuf.f3 == myTmpTable2.f2
	{
		this.myCustomLogic(myTmpTable2);
	}
}

    If the query returns nothing, it means that you don't have any data meeting the conditions. I have no idea whatr F2 and F3 means and why you're trying to compare them, but it seems that myTmpTable1.F2 already contains all values that can occur in F2 (for the given ID) and therefore the query with notexists join returns nothing.

    By the way, do you realize that myTmpTable2 contain values just for a single ID? I'm not sure that it was you intention. I even suspect that the ID isn't populated at all. Maybe the problem is in your expectations about the data and not in the code of the select statement. Review the content of your temporary tables and compare it with your expectations.

    By the way, which type of temporary table are you using?

  • Mav Profile Picture
    Mav on at

    Just noticed another possible limitation with X++ and d365 ERP the below code of line works fine when i set myTempTable as regular type & gives error shared below as soon as i change myTempTable to tempDb type.

    You need not know what is f1 to f5 , it can be anything & is purely irrelevant .

    Code

       insert_recordset myTmpTable (Id, F1, F2, F3, F4, F5, RefCreatedDateTime, RefRecId)                                         
                    select Id, d, F1, F2, F3, F4, F5, CreatedDateTime, recid from   myTable

    As regular table above code works fine.

    As temptable it gives this error

    "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."

  • Mav Profile Picture
    Mav on at

    Ok so for now i am changing my code to skip above limitation by x , however even after doing that I cannot get dataset of 1 tmptable buffer to other tmptablebuffer.

    Does the setTmpData() not transfers data set amongst tmptable buffers?

    Please suggest a way to copy data set from tmptablebuffer1 to tmptablebuffer2 .

    I have used

    tmptablebuffer2.setTmpData(tmptablebuffer1) but it does not work. Shared below is my revised code, the tmptablebuffer1 has large data set & works fine , i want this large data set to go into my tmptablebuffer2 so as to minimize db sql trips.

    My Method()
{
    Mytable     myTable;
    MytableTmp  myTmpTable1,myTmpTable2;
    
    insert_recordset myTmpTable1 (Id, f1,f2,f3)                                       
        select Id, f1, f2, f3,  from   myTable
        where id != '';       
           
            myTmpTable2.setTmpData(myTmpTable1);

            while select firstonly10 Id  from  myTmpTable1                            //This whie works FINE, i can see the INFO test successful for  myTmpTable1
                group by id
                
            {
                //myTmpTable2.setTmpData(myTmpTable1);                                  //Also have tried using setTmpData within while loop & still get same issue, i.e. no value in myTmpTable2
                //myTmpTable3.setTmpData(myTmpTable1);
               
               info (myTmpTable1.Id);                                                  //Can see this info working just fine
               
                while select   myTmpTable2                                              //Never enters this while loop
                  where myTmpTable2.id == myTmpTable1.id 
                    {
                      info (myTmpTable1.Id ,myTmpTable12.id , myTmpTable13.id);
                    }
                        
            }
   }

  • Martin Dráb Profile Picture
    Martin Dráb 237,976 Most Valuable Professional on at

    First of all, please answer the question about which kind of temporary table you're using. Is it InMemory or TempDB? As I explained above, whether you should use setTmpData() or linkPhysicalTableInstance() depends on the type.

    Note that putting a large data set to an InMemory table buffer would allow you to query the data without reaching database again, but the whole solution could easily be slower than if you query database. The database server is designed for dealing with large data sets, while InMemory tables are intended for small amounts of data (ideally those that you can put into memory). And if you use TempDB table, you're going to query DB anyway, therefore it doesn't mean your stated requirements.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 456 Super User 2025 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 429 Most Valuable Professional

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans