+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Very slow if $data is big (/showthread.php?tid=86193)
Pages:
1
2
Very slow if $data is big - MarieveStrange - 01-19-2023
Hello everyone,
I'm new on this forum but I'm using CI since approx 15 years.
I need your help to optimise a website built with CI4.
Some pages are very slow to load and after a lot of tests, I've realized that it's because the $data variable I'm passing to the view is very big. But I need all my datas to show everything correctly in the page content.
Can I do something to accellerate page loads when $data is very big? Can I compress it in a way?
Or is there another way to pass variables to view? Should I go with Ajax calls?
Thanks in advanced for your ideas and suggestions.
RE: Very slow if $data is big - InsiteFX - 01-20-2023
One way of sppeding it up is to use the php.net Opcache.
php.net - OPcache
Code:
PHP.INI
[Opcache]
zend_extension=/full/path/to/opcache.so (nix)
zend_extension=C:\path\to\php_opcache.dll (win)
Note that when the path contains spaces you should wrap it in quotes:
zend_extension="C:\Program Files\PHP5.5\ext\php_opcache.dll"
opcache.enable = 1
opcache.memory_consumption = 512
opcache.interned_strings_buffer = 64
opcache.max_accelerated_files = 32531
opcache.validate_timestamps = 0
opcache.save_comments = 1
opcache.fast_shutdown = 0How to use PHP OPCache?
RE: Very slow if $data is big - nc03061981 - 01-20-2023
Perhaps ask yourself: what do you really need from such a large amount of data? Let's statistics them and display them instead of displaying such a large amount of data.
RE: Very slow if $data is big - davis.lasis - 01-20-2023
Huge bottle neck is DateTime class auto convertion
- If you are using models with timestamps (updated_at and created_at)
PHP Code:
protected $useTimestamps = true;
- if you are pulling data with entities
PHP Code:
class User extends Entity
{
protected $dates = ['created_at', 'updated_at', 'deleted_at'];
}
Clear $dates array and see if that helps, then - make your decisions
RE: Very slow if $data is big - php_rocs - 01-20-2023
@MarieveStrange , I would be curious to see if your queries are efficient. What test have you run to determine your issues?
RE: Very slow if $data is big - sheilaf - 01-20-2023
How much data are you sending to the view?
Tell us more about your application. If you're reloading the whole page after a small change, then Ajax might indeed be the way to go. Hard to say without knowing more.
RE: Very slow if $data is big - InsiteFX - 01-21-2023
You can run your queries in PhpMyAdmin using the Explain it will advise you on your query.
RE: Very slow if $data is big - MarieveStrange - 01-23-2023
Thanks everyone for wanting to help me!
My main query looks like this:
Code:
SELECT `c`.*, `p`.`nom` as `projet_nom`, `p`.`entente_paiement`, GROUP_CONCAT(cc.id_client SEPARATOR "|") AS id_clients, `cie`.`nom` as `cie_nom`, `f`.`id` as `id_facture`, `f`.`date_envoi`, `f`.`total`, `f`.`date_facture`, `ff`.`id` as `id_facture_finale`, `ff`.`total` as `total_finale`, `ff`.`date_facture` as `date_facture_finale`, `au`.`first_name` as `user_prenom`, `au`.`last_name` as `user_nom`, `au2`.`first_name` as `verif_prenom`, `au2`.`last_name` as `verif_nom`, `ef`.`id_encaissement`, SUM(ef.montant) as montant_encaissement, `ef2`.`id_encaissement` as `id_encaissement_finale`, SUM(ef2.montant) as montant_encaissement_finale, MAX(cp.date_prolongation) as date_prolongation FROM `commandes` `c` LEFT JOIN `projets` as `p` ON `c`.`id_projet` = `p`.`id` LEFT JOIN `compagnies` as `cie` ON `cie`.`id` = `p`.`id_compagnie` LEFT JOIN `commandes_prolongation` as `cp` ON `c`.`id` = `cp`.`id_commande` LEFT JOIN `commandes_clients` `cc` ON `cc`.`id_commande` = `c`.`id` LEFT JOIN `factures` `f` ON `f`.`id_commande` = `c`.`id` AND `f`.`type` = "commande" LEFT JOIN `factures` `ff` ON `ff`.`id_commande` = `c`.`id` AND `ff`.`type` = "finale" LEFT JOIN `encaissements_factures` `ef` ON `f`.`id` = `ef`.`id_facture` AND `ef`.`type` = "facture" LEFT JOIN `encaissements_factures` `ef2` ON `ff`.`id` = `ef2`.`id_facture` AND `ef2`.`type` = "facture" LEFT JOIN `auth_users` `au` ON `au`.`id` = `c`.`created_by` LEFT JOIN `auth_users` `au2` ON `au2`.`id` = `c`.`verificateur` GROUP BY `c`.`id`It join 11 tables, but the query is executing very fast (0,0056 second(s)). It gave 415 results.
I'm using the datas passed to the view to load a table of orders (rental of products), indicating the number of products, the total of the invoices, the list of the clients associated with the orders, the balance of each order, etc.
I know that the problem comes from the amount of data passed to the view because when I empty the view (showing only the HTML frame with no data), it's still very slow. When I'm doing a query with not a lot of results, the page loads at a normal speed.
Another problem that I have is that I'm calling 4 queries before the page loads. One other query makes it slow (multiple array sent to the view is too big). So when I'm loading the page, it's very very slow. My clients tell me that it takes almost 30 seconds to load. On my side, it's a bit quicker but I have a very fast computer.
Thanks for your help and suggestions!
RE: Very slow if $data is big - superior - 01-23-2023
What browser do you use?
In Chrome you have the extension Page load time that will tell you how long each request took.
Maybe that would explain the part that is slow delivering the information.
415 results doesn't seem that much to me, i've been working with way more results inside CodeIgniter 4...
RE: Very slow if $data is big - MarieveStrange - 01-24-2023
Thanks superior! I've activated the extension as suggested, here is the result:
Redirect 0
DNS 0
Connect 0
Request 10090
Response 1
DOM 1328
Parse 762
Execute Scripts 0
Content loaded 1
Sub Resources 565
Load event 1
Total 11422
So it's the "request" part of it that is slow. Is it telling that the slowness is created by mysql queries? When I execute the queries in CI and print the result, it's quite fast. Following my tests, the slowness happen when I pass the result variables to the view, but maybe i'm wrong.
I've also tested to only get few results from the queries and the speed seems normal.
I know that 415 results is not very big, but I get multiple arrays of multiple objects puts together in $data variable that is passed to the view, and that's what CI doesn't seems to like.
Can I do other tests to have more precisions on the problem?
Thanks for your help!