During report development we populate temp table and bind it to SSRS report. And most of the time we were doing one to one mapping inside while loop or query run loop., Kindly consider following code snippet.
While select sum(Qty) from Inventtrans group by ItemId, InventDimId , DatePhysical Where datePahyical > _fromdaet && datePhayical <_todate { Tmp.ItemId= inventrans.ItemId; Tmp.IventDim = inventtrans.InventId; Tmp.Qty = inventtrans.QTY; Tmp.Insert(); }
But if we replace loop with Insert_RecordSet , report execution time reduce much. Consider following code snippet used to replace loop with Insert_Recordset
Insert_recordset Tmp (ItemId,InventdimId,QTY) Select forceplaceholders ItemId, InventDimId, sum(QTY) From inventtrans Group by ItemId,InventDimId Where datePahyical > _fromdaet && datePhayical <_todate;