Level 73
Impossible to say what is going on here, but I would guess that you have a likely bad SQL query or two in there. You probably need to create an index or two in your database.
2 likes
Sep 25, 2024
10
Level 1
Laravel 10 + Datatables, Very slow to render 10 records.
Hi there.
I have a 2 same projects..
- Encoding System - Old One ( Laravel 8 , PHP8.0 )
- Encoding System - Revamp ( Laravel 10, PHP8.3 )
the old system has almost 80,000 encoded datas. and it has no problem loading 100 records on first page of Datatables. it only took 1-3 seconds.. and it has more columns compare to the new system..
but the new system can't barely load 10 records.. sometimes i have to wait 2 - 3 minutes before it loads the records.. the total encoded datas is almost 26,000 only compare to the old one.
Im using the same codes, actually the new one uses much less joined tables. in javascript part, the old one is much more complicated or more line codes. but the settings for 'processing' and 'serverSide' were both set to TRUE
** Take note, both projects were hosted on the same web hosting that we are using. **
Level 1
@Tray2 Hello there. But this is the only query im using in my Latest/Revamp Project.
$data = EncodedDatas::select(
'encoded_datas.id as dataId',
'encoded_datas.data_employee_no',
'encoded_datas.data_user_firstname',
'encoded_datas.data_user_middlename',
'encoded_datas.data_user_lastname',
'encoded_datas.data_encoded_by',
'encoded_datas.data_user_referred_by',
'encoded_datas.created_at',
'encoded_datas.data_user_lagunanay',
'encoded_datas.data_user_lagunanay_id',
'encoded_datas.data_voter_status',
'encoded_datas.data_user_image',
'barangay_list.id as barangayId',
'barangay_list.name as barangayName'
)
->leftJoin('barangay_list', 'barangay_list.id', '=', 'encoded_datas.data_user_barangay')
->orderBy('encoded_datas.created_at', 'DESC')
->get();
and this one is from the old project:
$data = EncodedDatas::join('barangay_list', 'barangay_list.brgy_id', '=', 'encoded_datas.data_user_barangay')
->join('barangay_position', 'barangay_position.pos_id', '=', 'encoded_datas.data_user_brgy_position')
->join('team_groups', 'team_groups.group_id', '=', 'encoded_datas.data_user_teamgroup')
->join('precint_list', 'precint_list.precint_id', '=', 'encoded_datas.data_user_precintno')
->join('cluster_list', 'cluster_list.cluster_id', '=', 'encoded_datas.data_user_clusterno')
->join('wave_list', 'wave_list.wave_id', '=', 'encoded_datas.data_user_wave')
->join('users', 'users.id', '=', 'encoded_datas.data_encoded_by')
->select(
'encoded_datas.data_id',
'encoded_datas.data_employee_no',
'encoded_datas.data_user_firstname',
'encoded_datas.data_user_lastname',
'encoded_datas.data_user_middlename',
'encoded_datas.data_user_barangay',
'encoded_datas.data_user_clusterno',
'encoded_datas.data_user_precintno',
'encoded_datas.data_user_brgy_position',
'encoded_datas.data_user_teamgroup',
'encoded_datas.data_user_referred_by',
'encoded_datas.data_encoded_by',
'encoded_datas.data_created',
'encoded_datas.data_voter_status',
'encoded_datas.data_user_asterisk',
'encoded_datas.data_user_raffle',
'barangay_list.brgy_id',
'barangay_list.brgy_name',
'barangay_position.pos_id',
'barangay_position.pos_name',
'team_groups.group_id',
'team_groups.group_name',
'users.id',
'users.fullname',
'precint_list.precint_id',
'precint_list.precint_number',
'cluster_list.cluster_id',
'cluster_list.cluster_number',
'wave_list.*'
)
->orderBy('encoded_datas.data_created', 'desc');
Level 51
Datatables - immediately reminds me of THAT javascript package, which loads all the records in and then 'paginates' it for you. Do as @tray2 suggests and improve your queries, if they need it.... as helpful backup, optimise the tables to actually paginate, etc
3 likes
Level 1
@jaseofspades88 yes only if serverSide processing was set to false. but if its 'true', it will only render the needed number of records per page. not all the entire table records.
1 like
Level 63
@itsthomyyy I don't think so, you query doesn't seem to load only the first lines. It loads the entire table and it's probably your datatable that filters to show only the first lines.
1 like
Level 75
I agree with @tray2 and @jaseofspades88 Also if you are using data tables you need to thoroughly read that documentation and do server side pagination.
We had one user here trying to load millions of records then paginate. A Big no no.
I further suggest learn how to write your own html tables.
1 like
Level 63
I remember a project started by other developers where the datatable loaded all the models from the database. It's really not the solution.
You should paginate the results of your query.
1 like
Level 1
I noticed something, i compare the old and new project.. The OLD one does not use ->get(); and it loads almost instantly. The NEW project uses ->get(); in the query.. I tried to remove it and try again, and YES it does do the trick. it loads the data instantly now..
Can anyone here explain how does this work?
1 like
Level 75
@itsthomyyy get() gets all instead of paginating.
1 like
Please or to participate in this conversation.