1
\$\begingroup\$

Currently i am using 2 queries - one for reattemptdate & one for holddate.... if we merge both queries & get the results in single query , than will it give better performance? if so how to merge ?

1st query :

$sql = "SELECT employeename, DATE(reattemptdate) as date, 
COUNT(*) as count FROM orders 
WHERE DATE(reattemptdate)  = CURDATE() 
GROUP BY employeename, date"; 
$results = $db_handle->runSelectQuery($sql); 
$numrowsresult =$results[0]['count'];


foreach ($results as $result) 
{
    echo $result['employeename'].'|'.$result['date'].'|'.$result['count']."<br>";
}

echo "<br>";

2nd query :

$sqla = "SELECT employeename, DATE(holddate) as date, 
COUNT(*) as count FROM orders 
WHERE DATE(holddate)  = CURDATE() 
GROUP BY employeename, date"; 
$resultsa = $db_handle->runSelectQuery($sqla); 
$numrowsresulta =$resultsa[0]['count'];


foreach ($resultsa as $resulta) 
{
    echo $resulta['employeename'].'|'.$resulta['date'].'|'.$resulta['count']."<br>";
}

echo "<br>";
\$\endgroup\$
  • \$\begingroup\$ what you're looking for is "UNION". The benefit might be only marginal, though, soo ... \$\endgroup\$ – Vogel612 Sep 10 '18 at 13:03
  • \$\begingroup\$ @Vogel612 Thanks, i will go through it.... \$\endgroup\$ – vickey colors Sep 10 '18 at 13:49
3
\$\begingroup\$

if we merge both queries & get the results in single query , then will it give better performance?

There are various aspects of performance here:

  • time
  • memory

My inclination is that you are leaning towards reducing time but maybe you mean memory or should at least consider it.

And consider that if the queries were merged into one, there are variables that would affect performance, including:

  • number of results
  • separation of data sets (i.e. display on-hold counts vs re-attempted counts in separate lists).

Generally it is best perform as few database queries as are necessary. If there were queries executed inside of a loop where data was selected based on a filter condition, it may make sense to perform one query and filter the data inside of the loop. But in this case, there are simply two similar but unique queries.

You didn't include the implementation for $db_handle->runSelectQuery() but the implementation might affect the answer as well. If that method uses a function to get all results at once (e.g. mysqli_fetch_all()) then it might be better for performance, but otherwise if it fetches results one at a time (e.g. using mysqli_fetch_all() until NULL is returned) then that might add a lot of overhead with looping over results in that method and then your code looping over the results for display purposes.

So in order to get a better answer, you must experiment with your data.

if so how to merge ?

There are multiple ways to do this. One approach is to simply use UNION. The SQL for the second SELECT query can be appended after the first query and the keyword UNION.

$sql = "SELECT employeename, DATE(reattemptdate) as date, 
COUNT(*) as count, 0 as Held FROM orders 
WHERE DATE(reattemptdate)  = CURDATE() 
GROUP BY employeename, date

UNION

SELECT employeename, DATE(holddate) as date, 
COUNT(*) as count, 1 as Held  FROM orders 
WHERE DATE(holddate)  = CURDATE() 
GROUP BY employeename, date
"; 

Notice also that those queries have an extra field Held. A field like that can be used to separate the data. That way after the results are fetched, they can be separated into the re-attempted and the held results - perhaps using array_filter().

//formerly $results
$reattemptedResults = array_filter($results, function($result) {
    return $result['Held'] == 0;
});
//formerly $resultsa 
$heldResults = array_filter($results, function($result) {
    return $result['Held'] == 1;
});

But keep in mind that this would be iterating over the results potentially 5 times- once to read rows from query results, once for each filtering and then once for each display (though it would be a subset), in that sense performance may actually be worse.

Another option is to use CASE in the queries to conditionally select and filter results:

$sql = "SELECT employeename, CASE WHEN DATE(reattemptdate)  = CURDATE() THEN DATE(reattemptdate) ELSE DATE(holddate) END as date, 
COUNT(*) as count, CASE DATE(holddate) WHEN CURDATE() THEN 1 ELSE 0 END as Held 
FROM orders 
WHERE DATE(reattemptdate)  = CURDATE() OR  DATE(holddate)  = CURDATE() 
GROUP BY employeename, date, Held"; 

And the data would still need to be filtered in PHP after the results are fetched. For small sets of data this likely won't be an issue but for large data sets it has a high potential. I suggest measuring the time with your data in both cases to see how the time compares. I tried it on a small set of data - one distinct employee name, two re-attempted orders and one held order on today's date. The query with CASE was slightly quicker than the original, even with two calls to filter the data using array_filter().

\$\endgroup\$
  • \$\begingroup\$ Thanks a lot for complete information.... now suppose if i use union, how to get count of reattempt & count of hold , means before i used $result['count'] & $resulta['count'] , now as i use only one query, how to get count for both ? \$\endgroup\$ – vickey colors Sep 11 '18 at 6:24
  • \$\begingroup\$ You can use count()- e.g. ‘count($reattemptedResults)` \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Sep 11 '18 at 6:31
0
\$\begingroup\$

if we merge both queries & get the results in single query , than will it give better performance?

No.

\$\endgroup\$
  • \$\begingroup\$ sorry, i accepted other answer as it gave more details..... \$\endgroup\$ – vickey colors Sep 11 '18 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.