Increase ProcessReport() Performance (optimize the Query) - SSRS Report

(0) Share

Report

Posted on by Muhammad_Ali

353

Hi -

I have a report that produces the data correctly the problem is the execution time, it takes a long time to generate the report. look at the screen shot below for my process report method.

     while select * from inventValueTransUnionAllParent
       join *  from inventDimParent
         group by InventLocationId,InventSiteId  ASC
            where inventValueTransUnionAllParent.inventDimId == inventDimParent.inventDimId
                  && inventDimParent.InventLocationId != ''
                  && inventDimParent.InventLocationId != 'N/A'
        {
             ttsBegin;
             MTRA_InventoryValueReportTmp.InventoryFinancialAmount         = this.populateFinancialAmount(fromDate,toDate,inventDimParent.InventLocationId);
            // MTRA_InventoryValueReportTmp.InventoryFinancialQty            = this.populateFinancialQty(fromDate,toDate,inventDimParent.InventLocationId);
            // MTRA_InventoryValueReportTmp.InventoryPhysicalPostedAmount    = this.populatePhysicalAmount(fromDate,toDate,inventDimParent.InventLocationId);
            // MTRA_InventoryValueReportTmp.InventoryPhysicalPostedQty       = this.populatePhysicallQty(fromDate,toDate,inventDimParent.InventLocationId);
             MTRA_InventoryValueReportTmp.InventLocationId                 = inventDimParent.InventLocationId;
             MTRA_InventoryValueReportTmp.InventSiteId                     = inventDimParent.InventSiteId;

             MTRA_InventoryValueReportTmp.insert();
             ttsCommit;
       }

Now for the individual methods which populated the tmp table buffer.

public CostAmount populateFinancialAmount(FromDate _fromDate, ToDate _toDate, InventLocationId _InventLocationId)
{
    CostAmount Amounts = 0;

      while select * from inventValueTransUnionAll
          where  inventValueTransUnionAll.TransDate >= _fromDate
            && inventValueTransUnionAll.TransDate <= _toDate
            && (inventValueTransUnionAll.unionAllBranchId == 6)
        join * from inventdim
          where inventValueTransUnionAll.inventDimId == inventdim.inventDimId
           && inventDim.InventLocationId == _InventLocationId
    {
        {
            Amounts = Amounts + inventValueTransUnionAll.Amount;
        }
    }
        while select * from inventValueTransUnionAll
          where  inventValueTransUnionAll.TransDate >= _fromDate
            && inventValueTransUnionAll.TransDate <= _toDate
            && (inventValueTransUnionAll.unionAllBranchId == 5)
        join * from inventDim
          where inventValueTransUnionAll.inventDimId == inventDim.inventDimId
           && inventDim.InventLocationId == _InventLocationId
    {
        {
            Amounts = Amounts + inventValueTransUnionAll.Amount;
        }
    }// working query

    return Amounts;
}

What changes I can make in my query/ Methods to optimize the report results ?

Any suggestions will be really helpfull

Thanks

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

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

Like (1)

Report
One obvious optimization is in populateFinancialAmount(). You fetch all fields of all records matching the conditions, while you really want just a single value - the sum of Amount. That's exactly what the aggregation function sum() is for. For example:

select sum(Amount) from inventValueTransUnionAll where inventValueTransUnionAll.TransDate >= _fromDate && inventValueTransUnionAll.TransDate <= _toDate && inventValueTransUnionAll.unionAllBranchId == 6 exists join from inventdim where inventValueTransUnionAll.inventDimId == inventdim.inventDimId && inventDim.InventLocationId == _InventLocationId; amounts = inventValueTransUnionAll.Amount;

And because you have it in a while loop, you fetch all fields of all records many times, which is an enormous waste of resources.

Was this reply helpful? Yes No
Muhammad_Ali 353 on at

Like (0)

Report

Thanks Martin for your advice. It did help me with performance. Do you think I can possibly make any changes in my ProcessReport() query to enhance performance even further ?

Thanks

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

Like (1)

Report

If you have two queries in your four populate* methods, it means eight queries for each record from the while select. If it returns 100 records, it means 800 extra records. This is what I would try to reduce, possibly even creating a single view with all the sums (calculated in computed columns).

You should also look at performance of individual queries. Start with Trace Parser and if you notice a problem, look at the execution plan in SQL Server Management Studio.

Was this reply helpful? Yes No
Muhammad_Ali 353 on at

Like (0)

Report

hmm Thanks Martin I will look into this now.

Much Appreciated

Was this reply helpful? Yes No

Community site session details

Increase ProcessReport() Performance (optimize the Query) - SSRS Report

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

Increase ProcessReport() Performance (optimize the Query) - SSRS Report

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