+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Performance loss large Database (/showthread.php?tid=61222)
Performance loss large Database - El Forum - 10-19-2014
[eluser]Unknown[/eluser]
Hi @all,
I am just migrating a project of mine into codeigniter. On one page the user is getting a list of over 300 tours and details of the tours.
So in my basic project I did the mysql-query and just ran a foreach loop and then in the foreach loop another mysql request to get all the details of each tour from a second details table.
Here is my codeigniter approach:
I have a model where all tours are loaded with one query. Foreach result in this query I do a
foreach($sql->result_array() as $tour) {
$tourarray[$tour['tourid']] = $tour;
and than again a subquery for the details which ends in a multidimensional array called $tourarray with a key per tour and then another subarray with all the details.
I return the $tourarray to the controller which loads the view.
In the view I have to go through the whole array again which ends up in ~6 seconds of loading time where my basic setup which did the same needed less than two.
Is there any shorter way to get a mysql result from a model to a view in codeigniter or is my approach totally wrong?
Thank you very much for your help.
Max
Performance loss large Database - El Forum - 10-19-2014
[eluser]jonez[/eluser]
Instead of doing one query to pull the ID's then 300 subqueries for tour details do it all as a single query join and delegate the array. It should not take 6s to compile a view from data, can you post some code on how you're building the view?
Performance loss large Database - El Forum - 10-19-2014
[eluser]Tpojka[/eluser]
Have you tried to join tables in one query in model?
What do your query codes look like? can you paste sample code examples of your query/subquery?
ed.
jonez was faster
Performance loss large Database - El Forum - 10-19-2014
[eluser]ivantcholakov[/eluser]
Code:
foreach($sql->result_array() as $tour) {This loads all the records within the memory, it is OK for smaller recordsets.
In CI3-dev there is a method unbuffered_row(), see http://ci3docs.cibonfire.com/database/results.html
I think, it cycles the returned recordset directly.
Code:
$query = $this->db->query("YOUR QUERY");
while ($row = $query->unbuffered_row())
{
echo $row->title;
echo $row->name;
echo $row->body;
}
// Variations, see the documentation:
// $query->unbuffered_row(); // object
// $query->unbuffered_row('object'); // object
// $query->unbuffered_row('array'); // associative arrayPerformance loss large Database - El Forum - 10-19-2014
[eluser]CroNiX[/eluser]
Also, do you have any fields indexed in the db besides primary key? Unless you construct the DB with proper indexes, queries get longer and longer with more data as it has to scan every single row to find results. I have real estate sites with literally millions of rows that only take about .5 seconds to run, and that includes joins on like 10 other tables.
Performance loss large Database - El Forum - 10-20-2014
[eluser]Unknown[/eluser]
Hi. Thanks for the replies.
The problem is, that each tour has like up 30 details segment. So a join would produce a result with the whole tourdata for 30 times, which isn't needed and would have to be sorted out afterwards.I don't think the query itself is the problem. I think it is the transfer between model->controller->view where the whole thing is looped several times.
Yes there are several indexes. As i said: The same database is beeing used by the "old version" which is performing very well.
The view is loaded pretty basic. The tourarray is returned to the controller where the view is loaded
$this->load->template('umlauf',$data);
the load->template is only including header and footer so I don't have to do it on each controller.
I will check out the unbuffered->row
Thanks so far.
Performance loss large Database - El Forum - 10-20-2014
[eluser]jonez[/eluser]
Correct, the join would result in one massive recordset (array). Pulling one query then delegating by ID in PHP is considerably faster than performing 300 individual queries. Try it and see for yourself.
Something like this should work well;
Code:
$results = $this->db->query( $sql, $params )->result_array( );
$data = array( );
foreach ( $results as $result ) {
if ( !array_key_exists( $result[ 'id' ], $data ) ) {
$data[ $result[ 'id' ] ] = array(
'id' => $result[ 'id' ],
'rows' => array( ),
);
}
$data[ $result[ 'id' ] ][ 'rows' ][ ] = $data;
}
unset( $result, $results );