manipulating a big data in form data source

(0) Share

Report

Posted on by ahmed.kimo

536

Hi,

i'm loading a big data in customized form but unfortunately it's taking too much time , i was thinking that if i can load it batch i mean load first 500 records then send it to the form datasource then load another 500 records till finish loading all the data , (separate thread for example) , or load next 500 records while scrolling the grid ,

Please advice what's it the best practice to achieve that ,

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Mea_ 60,284 on at

Like (0)

Report

Hi ahmed.kimo,

Could you please give us more details about how you "uploading" data to form datasource ?

Was this reply helpful? Yes No
ahmed.kimo 536 on at

Like (0)

Report

Hi ievgen Miroshnikov,

we are having a customized screen which has data from customized table , we are filling this table by fetching data from other table while opening the form itself ,

public void init()

{

Table1 table1;

Table2 table2

Table3 table3;

Tabletmp tabletmp;

while select * from table1

{

while select * from table2 where

table1.RefId==table2.RefId

{

select firstOnly * from table3 where

table3.Ref2Id==table2.Ref2Id;

tabletmp.RefId=table1.RefId;

tabletmp.ObjectName=table1.GetObjectName(); // here is display method that means another sub query

tabletmp.Ref2Id=table2.Ref2Id;

tabletmp.Ref3Id=table3.RecId;

tabletmp.TransactionDate=today();

tabletmp.TransactionDiscription="TransactionDiscription";

tabletmp.TransactionAmount=table1.Amount;

tabletmp.Insert();

{

}

// tabletmp == tabletmp_ds;

}

Was this reply helpful? Yes No
Martin Dráb 237,803 Most Valuable Professional on at

Like (0)

Report
No wonder it's slow - it's a very inefficient design.

First of all, never run such code on client - it has to transfer all data from database to AOS, then to the client computer, than back from the client computer to AOS and from AOS to database. If you run it on AOS, you'll avoid those two transfers between client and server.

Then get rid of those ugly and extremely wasteful nested while selects. This:

while select * from table2 join table1 where table1.RefId == table2.RefId

will be much faster than your code:

while select * from table1 { while select * from table2 where table1.RefId==table2.RefId {

Also, you said there is another subquery in the display method. Try to create a join instead of calling the method.

If you achieve it, you may be able to avoid fetching any data by simply sending a single insert_recordset command to database.

Was this reply helpful? Yes No
ahmed.kimo 536 on at

Like (0)

Report

As you see in my "Example" i return table1,table2 and table3 for sure that means this is just example to let you answer my question that how i can load the data in batches or partially , at the same time i can tell you a clear "Example" with clear code with same instructions which you sent , but also same slowness will come ,

HcmWorker hcmworker;

HcmloanTransactions hcmloanTransactions;

HcmLeaveTransactions hcmLeaveTransactions;

select * from hcmworker

join * from hcmloanTransactions

where hcmworker.Worker==hcmloanTransactions.Worker

join * from hcmLeaveTransactions

where hcmworker.Worker==hcmLeaveTransactions.Worker

hcmworker has 250,000 records

hcmloanTransactions has 750,000 records (for each employee/worker 3types of loans)

hcmLeaveTransactions has 3,000,000 records (at least one leave transaction for each employee per month),

hope you understand the idea now ,

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,803 Most Valuable Professional on at

Like (0)

Report

I had no other option than working with the example you gave us. If you're saying that your example doesn't represent your real scenario very well, pick up just those parts of my solution that are applicable (e.g. running on server, using insert_recordset) and ignore those that aren't (e.g. if you have no nested while selects). If you can't use insert_recordset, make sure you use field lists (instead of *) to avoid fetching data that you don't need).

If I was you, I would also question whether copying all the data makes sense in the first place.

If you want to create a batch, use the SysOperation framework.

Was this reply helpful? Yes No
ahmed.kimo 536 on at

Like (0)

Report

it's ok but i can see that for normal grid behavior microsoft is loading more data from the table into datasource while scrolling, i need to follow the same since my datasource is empty and i'm fetching the data on demand , and the problem is the count of returned records , so i need any way to let system load more data if user need to see more ,

for example user was searching for particular information in this inquiry , and he load the data and he keep searching in the current rows , if he found his information he just close this inquiry and no need to load other data but if he did not found his data definitely he will scroll down and see more at this moment i have to load more records , in this case user can use this screen on time , but if i'm loading all data one time that means user has to wait till loading all the data then he can start searching on the first records .

Was this reply helpful? Yes No
Martin Dráb 237,803 Most Valuable Professional on at

Like (0)

Report

If you want merely see data in a form, what's the point to copy data from between tables? That's a completely different thing.

If you want the standard behavior of for data source, just use it. If your second example is what you're trying to achieve, throw away all code and simply add three datasources (with inner joins) to your form.

Was this reply helpful? Yes No
ahmed.kimo 536 on at

Like (0)

Report

yes you are correct , but if you see Finance/subledger form in paystatment for example you can't see a clear data saved in the table but once you open that form system if fetching the required information from some setup and posting profile as well and push it in the form , same behavior we are following but for bigger data ,

Was this reply helpful? Yes No
ahmed.kimo 536 on at

Like (0)

Report

Thanks Martin Dráb SysOperation framework. is the best solution for my case ,

Was this reply helpful? Yes No

Community site session details

manipulating a big data in form data source

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

manipulating a big data in form data source

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates