Performance boost CI3

I have a script that has loops an array 1000s of times and in each loop runs 4 select queries. It takes one to several minutes to execute.
I need more speed.
I tried to "prepare" the queries before the loop with

PHP Code:

$query = "SELECT * FROM table WHERE id = ? AND d = ?";
foreach(foos as foo){
    $q = $this->db->query($query, [$id, $d])->row_array();
} 


Not speedier.
Then I try to re-write the queries so I could use free_result() at the end of each loop. Not speedier either. Any other suggestions?

PHP Code:

$query = "SELECT * FROM table WHERE id = ? AND d = ?";
foreach(foos as foo){
    $q = $this->db->query($query, [$id, $d]);
    $row = $q->row();
    // handle the result... 
    $q->free_result();
} 


@muuucho,

I would definitely look at your queries (especially the one that takes several minutes). Do you know how fast all of your queries are running? I would do some query analysis to determine how you can speed up your queries. It may be as simple as adding an index to speed up your queries. Based on what I see of your queries above (and assuming that you are not dealing with petabytes of data) your queries should be able to execute in milliseconds.

Are you using MySQL? Are you familiar with how to do a basic query analysis?

@muuucho,

I would definitely look at your queries (especially the one that takes several minutes).  Do you know how fast all of your queries are running?  I would do some query analysis to determine how you can speed up your queries. It may be as simple as adding an index to speed up your queries.  Based on what I see of your queries above (and assuming that you are not dealing with petabytes of data) your queries should be able to execute in milliseconds.

Are you using MySQL? Are you familiar with how to do a basic query analysis?

It is not a single query that takes long time. Also, I have indexed column "id" together with "d". It is thousands of queries that seems to take that time all together.  I am not familiar with query analysis.

Why don't you get the query all at once and then do what you need to in the foreach loop?

PHP Code:

if ($query->num_rows() > 0)
{
    foreach ($query->result_array() as $row)
    {
        $data[] = $row;
    }
}

$query->free_result();

// Now you can do what ever you need to with the data.
return $data; 


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

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Why don't you get the query all at once and then do what you need to in the foreach loop?

PHP Code:

if ($query->num_rows() > 0)
{
    foreach ($query->result_array() as $row)
    {
        $data[] = $row;
    }
}

$query->free_result();

// Now you can do what ever you need to with the data.
return $data; 


Because each of the 4 queries inside each loop depends upon each other. Maybe I can write it as subqueries, but do you think that would make a significant difference?

@muuucho,

Can you run the profiler (https://codeigniter.com/userguide3/gener...iling.html) on the page where the query runs? It will give us a lot of useful information about all the queries that run.

@muuucho,

Can you run the profiler (https://codeigniter.com/userguide3/gener...iling.html) on the page where the query runs?  It will give us a lot of useful information about all the queries that run.

Profiler tells me that all queries (about 10-50k) takes very little time each to execute, like  0,001s. Altogether though, it results in 30s up to several minutes. Could native prepared statements speed it up? Not that I am not impressed about the result, I really am, but if it is possible to speed it up, I really like to do so.

@muuucho,

If the queries are executing that fast then my next approach would be to see if we can build the query differently so that it gets all the data in one loop. Have you tried building out the i and d values into a list and then use that list in your query?

For example:
You would build out the $id and $d arrays then run the query.
$id = [1,2,3,4,5];
$d = [20,21,22,25];

$query = "SELECT * FROM table WHERE id in ? AND d in ?";
$q = $this->db->query($query, [$id, $d]);

@muuucho,

If the queries are executing that fast then my next approach would be to see if we can build the query differently so that it gets all the data in one loop.  Have you tried building out the i and d values into a list and then use that list in your query? 

For example:
You would build out the $id and $d arrays then run the query.
$id  = [1,2,3,4,5];
$d  = [20,21,22,25];

$query = "SELECT * FROM table WHERE id in ? AND d in ?";
    $q = $this->db->query($query, [$id, $d]);

Yeah, that "in" makes it a lot faster, thanks!

Be careful. The proposed option can play a cruel joke with you.
This request can be written as

Code:

SELECT *
FROM table
WHERE
(id = 1 AND (d = 20 OR d = 21 OR d = 22 OR d = 25))
OR (id = 2 AND (d = 20 OR d = 21 OR d = 22 OR d = 25))
OR (id = 3 AND (d = 20 OR d = 21 OR d = 22 OR d = 25))
OR (id = 4 AND (d = 20 OR d = 21 OR d = 22 OR d = 25))
OR (id = 5 AND (d = 20 OR d = 21 OR d = 22 OR d = 25))

Depending on the logic of your database, this may conflict with the conditions.
https://www.db-fiddle.com/f/ckHvgzVN2u8a1dtv7GoNMZ/0

Perhaps you'd better use UNION