Finance | Project Operations, Human Resources, ...

Invent Trans query object is very very slow, how execute faster

(2) Share

Report

Posted on by Sachin Mittal

I have created a SSRS report with DP class, to get data from invent Trans and created a query object with following code to get the data, but report take more than 1 hour to generate, that is not efficient at all, how to execute so that i can get results in max 5 min or so.

 Query query = new Query();               qbds = query.addDataSource(tableNum(InventTrans));        qbds.addRange(fieldNum(InventTrans, DatePhysical)).value(SysQuery::range(dateNull(),asOfDate));                qbds.addSortField(fieldNum(InventTrans, ItemId));        if(item)        {            qbds.addRange(fieldNum(InventTrans, ItemId)).value(item);        }        qbdsDim = qbds.addDataSource(tableNum(InventDim));                qbdsDim.relations(true);        if(inventSiteid)        {            qbdsDim.addRange(fieldNum(InventDim, InventSiteId)).value(inventSiteid);        }        if(inventLocationId)        {            qbdsDim.addRange(fieldNum(InventDim, InventLocationId)).value(inventLocationId);        }                QueryRun queryRun = new QueryRun(query);        int i;        while (queryRun.next())        {        ///Report Logic to get Data and insert in temporaray table        }

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Suggested answer

Layan Jwei 8,280 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sachin,

Maybe start by improving your query. For example, you can start by selecting only the fields you need from the query and not all of them.

Also you need to identify if the query is the root cause for the slowness or not (which most probably is)

You can do that by running the report with trace parser to see which part took the most time.

Also you can take the sql query and run in sql server management system and look at the execution plan to see which part is taking time and if u can improve it.

After you improve the query, if it's still taking time and you don't think there is anything else you can improve in code, then you can make your RDP class extends SRSReportDataProviderPreProcessTempDB instead of SRSReportDataProviderBase

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/prevent-long-running-reports-timing-out

Thanks,

Layan Jweihan

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future.

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

Like
a
(0)

Report
Copy link

Link copied!

Please show us the actual query. We can't know what SQL query your code generated because it depends on some variables. It's even possible that the query fetches all inventory transactions in the given company, which may be a huge amount of data.

Also, the fact that the report takes an hour to generate doesn't mean that this query is to blame. For example, a bigger problem may be in your code saving the data. You should analyze the situation, otherwise you may be wasting time with a wrong problem. I would start by debugging this code in isolation instead of doing it through the report.

Was this reply helpful? Yes No
Raj Borad 1,578 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sachin,

can you please try below query to run in SSMS and check whether it improves the performance or not.

select * from inventTrans
where inventTrans.DatePhysical >= dateNull()
and inventTrans.DatePhysical <= asOfDate
and ( !item || inventTrans.ItemId == item )
join inventDim
oninventDim.InventTransId == inventTrans.RecId
and ( !inventSiteId || inventDim.InventSiteId == inventSiteId )
and ( !inventLocationId || inventDim.InventLocationId == inventLocationId )
order by inventTrans.ItemId

Thank you,

Raj D Borad

Was this reply helpful? Yes No
Waed Ayyad 9,212 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi,

Reformatting your code for better understanding it. You should try to run your report without adding logic on the on While of the Query in order to define the source of the issue. If it is still taking a lot of time, then try to reduce your query data sources one by one to check which part is consuming a lot of time (has a lot of data).

Query query = new Query(); qbds = query.addDataSource(tableNum(InventTrans)); qbds.addRange(fieldNum(InventTrans, DatePhysical)).value(SysQuery::range(dateNull(),asOfDate)); qbds.addSortField(fieldNum(InventTrans, ItemId)); if(item) { qbds.addRange(fieldNum(InventTrans, ItemId)).value(item); } qbdsDim = qbds.addDataSource(tableNum(InventDim)); qbdsDim.relations(true); if(inventSiteid) { qbdsDim.addRange(fieldNum(InventDim, InventSiteId)).value(inventSiteid); } if(inventLocationId) { qbdsDim.addRange(fieldNum(InventDim, InventLocationId)).value(inventLocationId); } QueryRun queryRun = new QueryRun(query); int i; while (queryRun.next()) { ///Report Logic to get Data and insert in temporaray table }

Thanks,
Waed Ayyad
If this helped, please mark it as "Verified" for others facing the same issue.


Was this reply helpful? Yes No