0
<?php

$db_conn = ocilogon("root", "admin", "//localhost/xe");


$cmdstr1 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_1%' ";
$cmdstr2 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_1%' and RESULT='0'";
$parsed1 = ociparse($db_conn, $cmdstr1);
$parsed2 = ociparse($db_conn, $cmdstr2);
ociexecute($parsed1);
ociexecute($parsed2);
$nrows1 = ocifetchstatement($parsed1, $results);
$nrows2 = ocifetchstatement($parsed2, $results);


$cmdstr3 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_2%' ";
$cmdstr4 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_2%' and RESULT='0'";
$parsed3 = ociparse($db_conn, $cmdstr3);
$parsed4 = ociparse($db_conn, $cmdstr4);
ociexecute($parsed3);
ociexecute($parsed4);
$nrows3 = ocifetchstatement($parsed3, $results);
$nrows4 = ocifetchstatement($parsed4, $results);



echo "<center><table border='1' align='top' bordercolor='gray' cellspacing='0' cellpadding='5'> \n";


$newCount = $nrows1;
$newCount1 = $nrows2;
$newCount2 = number_format(($nrows2/$nrows1)*100,0,'.','');

$newCount3 = $nrows3;
$newCount4 = $nrows4;
$newCount5 = number_format(($nrows4/$nrows3)*100,0,'.','');


echo "<tr><td colspan='2'>SYSTEM_1</td>";
echo "<td>$newCount</td>";
echo "<td>$newCount1</td>";
echo "<td>$newCount2 %</td></tr>";

echo "<tr><td colspan='2'> SYSTEM_2</td>";
echo "<td>$newCount3</td>";
echo "<td>$newCount4</td>";
echo "<td>$newCount5 %</td></tr>";



echo "</tr></table>";


echo "<br><em>TEST OK!</em><br></center></body></html>\n";

This script present percent of availability of 30 Systems :
but when I add all systems and execute query for every systems :

$cmdstr1 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_1%' ";
$cmdstr2 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_1%' and RESULT='0'";

$cmdstr3 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_2%' ";
$cmdstr4 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_2%' and RESULT='0'";

$cmdstr5 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_3%' ";
$cmdstr6 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_3%' and RESULT='0'";

$cmdstr7 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_4%' ";
$cmdstr8 = "SELECT  *FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_4%' and RESULT='0'";

.....

$cmdstr50

Then script is very not efficient.. :(
so as to not receive from server timeouts
increase the parameter in php.ini> max_execution_time = 120 or more does not help

, how to improve this script

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by pritaeas
1

A like condition is very slow to execute. From what I can see in your queries, they are identical, except for the SYSTEM_ part. So why don't you change the query to:

SELECT * FROM TESTS WHERE TEST_TIME BETWEEN TO_DATE('2012-01-01','YYYY-MM-DD') AND TO_DATE('2012-02-02','YYYY-MM-DD')+1 and SYSTEM_NAME like '%SYSTEM_%'

and do the additional processing in PHP? Is there no way to remove/improve the like, for example by removing the first % sign? If you can provide some sample data, then perhaps we can adjust the query some more.

Edited by pritaeas

Votes + Comments
Simple, but effective method to improve efficient
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.