Very slow if $data is big

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.  

--
MarieveStrange
Web Programmer

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 = 0

How to use PHP OPCache?

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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.

Learning CI4 from my works, from errors and how to fix bugs in the community

Love CI & Thanks CI Teams

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

@MarieveStrange , I would be curious to see if your queries are efficient. What test have you run to determine your issues?

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.

You can run your queries in PhpMyAdmin using the Explain it will advise you on your query.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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!

--
MarieveStrange
Web Programmer

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...

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!

--
MarieveStrange
Web Programmer