Dynamics Community Forum Thread Details

x++ Group by slows down execution

Hey guys!

I created an SQL Statement in x++:

select ItemId, DataAreaId, sum(Qty)
    from reqTrans join reqPlanVersion
                  join inventTable
                  join Name from ecoResProductTranslation
                  join InventLocationId from inventDim
                  join InventLocationId from inventDim
    group by reqTrans.ItemId, reqTrans.dataAreaId, ecoResProductTranslation.Name, inventDim.InventLocationId
    where reqTrans.PlanVersion == reqPlanVersion.RecId
    && (reqPlanVersion.ReqPlanId == 'Statisch' || reqPlanVersion.ReqPlanId == 'Static')
    && reqPlanVersion.ReqPlanDataAreaId == reqTrans.dataAreaId
    && reqPlanVersion.Partition == reqTrans.Partition
    && reqTrans.ItemId == inventTable.ItemId
    && !(inventTable.ItemId like '300.___.___')
    && !(inventTable.ItemId like '900.___.___')
    && inventTable.PmfProductType == 0
    && inventTable.dataAreaId == reqTrans.dataAreaId
    && inventTable.Partition == reqTrans.Partition
    && inventTable.Product == ecoResProductTranslation.Product
    && inventTable.Partition == ecoResProductTranslation.Partition
    && reqTrans.CovInventDimId == inventDim.inventDimId
    && reqTrans.dataAreaId == inventDim.dataAreaId
    && reqTrans.Partition == inventDim.Partition
    && reqTrans.RefType == 10 || reqTrans.RefType == 12 || reqTrans.RefType == 14 || reqTrans.RefType == 21 || reqTrans.RefType == 32;

This Statement can not execute in an finit time. I tried it and after 2 Hours it was still working.

Without the "Group by", the whole Thing only lasts for 10 seconds. Whats the Problem here?

If I execute the whole Thing, with the Group by, on the SQL Server itself in the MS Management Studio, it takes only 1 second.

There cannot be such a big difference in time.

I hope that someone can help me with my Problem.

THANKS!

Quick Links