groupBy taking lots of time, is there any solution for that?

[techmahedy]

My query performs well when I am not using group by. But with groupBy, it is taking lots of time to load data. My query:

Code snippet of problem

`$query = DB::table('OrderInvoiceDetails as OID')
        ->join('OrderInvoiceMaster as OIM', 'OIM.InvoiceID', '=', 'OID.InvoiceID')
        ->join('DBMasterCode as DM', 'DM.MasterCode', '=', 'OIM.DistributorCode')
        ->join('SDMSCustomer as CU','CU.TTYCode', '=', 'DM.TTYCode')
        ->join('SDMSProduct as P', 'P.ProductCode', '=', 'OID.ProductCode')
        ->join('SR as S', 'S.SRCode', '=', 'OIM.SRCode')
        ->join('Customer as C', 'C.CustomerCode', '=', 'OIM.CustomerCode')
        ->join('ChannelType as CT', 'CT.ChannelType', '=', 'C.ChannelType')
        ->join('Beat as B', 'B.BeatCode', '=', 'OIM.BeatCode')
        ->select("OIM.InvoiceNo","OIM.DistributorCode AS Distributor_Code","OIM.SRCode","OIM.BeatCode","OIM.CustomerCode","OIM.OrderDate AS Order_Date","OIM.DeliveryDate as Delivery_Date","OID.ProductCode","OID.DeliveryQty","DM.DBMasterName","CU.Level3Name","CU.Level2Name","CU.Level1Name","P.ProductName","S.SRName","B.BeatName","P.BrandCode","DM.DBLocation","CU.TTYCode","CU.TTYName","CT.ChannelDesc","C.CustomerName",DB::raw("SUM(OID.BonusQTY) as BonusQTY"),DB::raw("SUM(OID.SalesQTY) as SalesQTY"),DB::raw("SUM(OID.Discount) as Discount"),DB::raw("SUM(OID.UnitPrice) as UnitPrice"),DB::raw("SUM(OID.SalesQTY*OID.UnitPrice) as OrderValue"))
        ->groupBy('OIM.InvoiceNo','OIM.OrderDate','P.BrandCode','OID.ProductCode','P.ProductName','OIM.DistributorCode','OID.UnitPrice','OIM.DistributorCode','DM.DBMasterName','DM.DBLocation','CU.TTYCode','CU.TTYName','CU.Level1Name','CU.Level2Name','CU.Level3Name','OIM.CustomerCode','C.CustomerName','CT.ChannelDesc','OIM.SRCode','S.SRName','OIM.BeatCode','B.BeatName','OIM.DeliveryDate','OID.SalesQTY','OID.DeliveryQty')
        ->when($DateFrom,function($query) use($DateFrom,$DateTo){
            $query->whereBetween('OIM.OrderDate',[$DateFrom,$DateTo]);
        });
`

System details

• Operating System (Windows 11)
• PHP 7.3
• Laravel Version (8)
• Laravel-Datatables Version (9.18)

[yajra]

I suggest you enable APP_DEBUG=true, inspect the network request and review the queries used/generated. You can then review which query is slow and needs optimization.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.