CODEWITHSUNDEEP
laraveldatatableloading
Noman Zahid
Noman Zahid

Reputation: 76

I am getting data from my database using Datatable, but it takes too much time to load

I am getting data from products table, i have use paginate in datatable which shows only 50 records first time, and when pager update to 200 rows it takes 12 to 13sec. Here is my Code. Thanks in Advance

    $query = Product::query();
    $query->with('def_or_last_supplier','units','prouctImages','productType',
    'productBrand','productSubCategory',
    'supplier_products','productCategory')
    ->where('status',1)->orderBy('refrence_no', 'DESC');

    if($request->default_supplier != '')
    {
        $supplier_query = $request->default_supplier;
        $query = $query->whereIn('id', SupplierProducts::select('product_id')->where('supplier_id',$supplier_query)->pluck('product_id'));
    }

    if($request->prod_type != '')
    {
        $query->where('type_id', $request->prod_type)->where('status',1)->orderBy('refrence_no', 'DESC');
    }

    if($request->prod_category != '')
    {
        $query->where('category_id', $request->prod_category)->where('status',1)->orderBy('refrence_no', 'DESC');
    }

    if($request->filter != '')
    {
        if($request->filter == 'stock')
        {
            $query = $query->whereIn('id',WarehouseProduct::select('product_id')->where('current_quantity','>',0.005)->pluck('product_id'));
        }
        elseif($request->filter == 'reorder')
        {
            $query->where('min_stock','>',0);
        }
    }

Upvotes: 0

Views: 145

Answers (1)

AH.Pooladvand
AH.Pooladvand

Reputation: 2059

Your query isn't that big but I think maybe result data size is a bit big
so what you can do is:

1: in my experience is to try to select your relations fields too you can do that like so

Source

Product::with('def_or_last_supplier',
    'units:id,created_at,updated_at', // Just add :field_1,field_2,another_field
    'prouctImages:id,field_1,field_2'
    ....
)

2: I suggest you to take advantage of lazy loading. if it's possible don't eager load all your relations at once load them where it's needed

Source

$products = Product::get()

In blade or somewhere in application
under a special condition I need to access to my relation, I can easily do that by
$products->load('my_relation_name')

3: I see a lot of orderyBy clauses in your code, what you can do is to index the fields that are searchable or needs to be ordered. it can be done by adding ->index() in your migration file

Source

public function up()
{
    Schema::create('products', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('refrence_no')->index();
        $table->timestamps();
    });
}

4: and finally you can always try to cache your queries to prevent unnecessary queries to database, this is how cache works

source

$products = Cache::remember('cache_name', $how_long_in_seconds, function () {
    return Product::get();
});

And finally here is the eloquent way of your query 

$query = \Cache::remember(json_encode($request->toArray()), 300, function () use ($request) {
    return Product::with('def_or_last_supplier',
        'units',
        'prouctImages',
        'productType',
        'productBrand',
        'productSubCategory',
        'supplier_products',
        'productCategory'
    )
        ->where('status', 1)
        ->orderByDesc('refrence_no')
        ->when($request->filled('default_supplier'),  function (Builder $query) use ($request) {
            $productIds = SupplierProducts::select('product_id')->where('supplier_id', $request->input('default_supplier'))->pluck('product_id');
            $query->whereIn('id', $productIds);
        })->when($request->filled('prod_type'),  function (Builder $query) use ($request) {
            $query->where('type_id', $request->input('prod_type'))->where('status', 1)->orderByDesc('refrence_no');
        })->when($request->filled('prod_category'),  function (Builder $query) use ($request) {
            $query->where('category_id', $request->input('prod_category'))->where('status', 1)->orderByDesc('refrence_no');
        })->when($request->filled('filter'),  function (Builder $query) use ($request) {
            $query->when('stock' === $request->input('filter'),  function (Builder $query) {
                $query->whereIn('id', WarehouseProduct::select('product_id')->where('current_quantity', '>', 0.005)->pluck('product_id'));
            })->when('reorder' === $request->input('filter'),  function (Builder $query) {
                $query->where('min_stock', '>', 0);
            });
        })->get();
});

Upvotes: 1

Related Questions