slow looping through while select query

Finance and Operations/AX Developer Forum
1

Hi there,

I have written a class that slects items (with dimension) sold in the last specified period (D/W/M) and writes to a temporary table.

Then I loop through these items finding its sale, if any, in the last n number of months and find write to another table. My aim is to work out average number sold per month for each of the items in the temporary table. Here, the user specifies number of months to work out average. I go through Inventtrans to get sales items within the period.

My problem is, when there are lots of records in the temporary table (eg. sold in the last one month), then the looping through the records in a while select query runs very very slowly.

Is there any way to speed up the process?

Greatly appreciate any help.

Thanks

Uma

2

Hi

I solved the problem doing a couple of things. I cahnged the temporary table to a permanent one. Then I used “exists join” to connect Inventtrans to this table and extracted the dataset to a second table in one-go. I used Insert_Recordset and exists join. This works fast.

Uma