1
\$\begingroup\$

I have written an application for lawyers' offices. There is an API to fetch info about court cases. I have implemented API calls for front end when user enters particular case; one tab is filled with info from this API about this case's state in court.

This code is run from a daily cron job; for all the cases in my application, it checks the last updated filed and matches it with last saved last updated filed in my DB. If they don't match, then the notification system in my app informs assigned people of the changes on that case.

So in short: automatic notification system about changes on all court cases in my app and email.

I'm a bit new to curl and PHP API calls, usually using JS, so bear with me.

I have some performance issues with this script. The DB call and loop is only around 70 times. And it's a bit slow. I'm afraid whether it will even work at all with bigger numbers.

Side note: API does not have option to fetch all data at once. I have to loop it so:

  1. First API call is made in function eSudovi() and data is saved $eSudovi.
  2. There is a function function findSudCode($val, $eSudovi) to filter that data and get some info from it in loops.
  3. DB get data is made and for each row one new API Call is made with function ePredmeti($sud, $pred) with $epredmet = ePredmeti($sudCode, $sudBroj); inside loop.
  4. I stopped here because it is pretty slow. And I will need additional logic inside loop.

As I'm new to curl in PHP I am looking for suggestions on improving this. I have been told something can be maybe done with opening and closing connections every time?

I have implemented checking of times, as recommended in Trying to access array offset on value of type null & isset problem in API call loop. It looks like the times are all over the place, not connected to type of response.

I'm using PHP version 7.4.

// ONE API CURL CALL AT START
function eSudovi()
{
    $endpoint = "xxx";
    $qry = '{"query":"query{sudovi {id, sudNaziv}}"}';

    $headers = array();
    $headers[] = 'Content-Type: application/json';

    $ch = curl_init();

    curl_setopt($ch, CURLOPT_URL, $endpoint);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $qry);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

    $result = curl_exec($ch);

    curl_close($ch);
    return json_decode($result, true);
}

// SAVE DATA FOR LATER USE
$eSudovi = eSudovi()["data"]["sudovi"];

//FILTER THAT DATA FROM LATER LOOP
function findSudCode($val, $eSudovi)
{
    foreach ($eSudovi  as $key => $value) {
        if ($value["sudNaziv"] == $val) {
            return $value["id"];
        }
    }
}


// 2nd API CURL CALL THAT IS USED IN LATER LOOP
function ePredmeti($sud, $pred)
{
    $endpoint = "xxx";
    $qry = '{"query":"query{ prvi:predmet(sud: ' . $sud . ', oznakaBroj: \"' . $pred . '\") {lastUpdateTime}}"}';

    $headers = array();
    $headers[] = 'Content-Type: application/json';

    $ch = curl_init();

    curl_setopt($ch, CURLOPT_URL, $endpoint);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $qry);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

    $result = curl_exec($ch);
    curl_close($ch);
    return json_decode($result, true);
}




// DB GET DATA

$results = mysqli_query($con, "
                SELECT DISTINCT predf_nas_br, predf_odv,  predf_SUD, predf_SUDBROJ
                FROM PREDMETIFView 
                WHERE predf_SUD <> '' AND predf_SUDBROJ <> '' AND predf_SUDBROJ NOT LIKE '% %'
                UNION ALL   
                SELECT DISTINCT predp_nas_br, predp_odv,  predp_SUD, predp_SUDBROJ 
                FROM PREDMETIPView 
                WHERE predp_SUD <> '' AND predp_SUDBROJ <> '' AND predp_SUDBROJ NOT LIKE '% %'
                ;");
while ($row = $results->fetch_assoc()) {

// LOOP THAT BD DATA
    foreach ($row as $key => $value) {
        if ($key == "predf_nas_br") {
            $nas = $value;
        }

        if ($key == "predf_SUD") {
            $sud = trim($value);
            if (!empty($sud) && isset($sud)) {
                $sudCode = findSudCode($sud, $eSudovi);
            }
        };

        if ($key == "predf_SUDBROJ") {
            $sudBroj = trim($value);
        };
       


        if (!empty($sudCode) && !empty($sudBroj) && isset($sudCode) && isset($sudBroj)) {
            // echo $sudCode . "<br>";
            // echo $sudBroj . "<br>";
            $epredmet = ePredmeti($sudCode, $sudBroj);

            print_r($epredmet);
            echo "<br>";

            if (isset($epredmet["data"]["prvi"]["lastUpdateTime"])) {
                $lastUpdateTime = $epredmet["data"]["prvi"]["lastUpdateTime"];
                $dateTime  = str_replace("T", " ", $lastUpdateTime);
                echo $nas . " - " . $dateTime . "<br>";
            }
        }
    }
};

EDIT:

To address anwser ive got:

Mysql Query took 0.0002 seconds for 52 results, and I don't see how would that be problem, it is done once and I said there is only around 70 results per testing DB. Anyway everything is properly indexed etc...

I have added user agent to curl and have implemented stackoverflow solution with class that should address reusing of curl connection.

I have also created code so you can all test it out with sample data and endpoints:

$arr = [
    ["Županijski sud u Zagrebu", "13-Kir-t-us-330/20"],
    ["Općinski građanski sud u Zagrebu", "P-2315/96"],
    ["Upravni sud u Splitu", "Uslgr-66/2020"],
    ["Općinski građanski sud u Zagrebu", "P-2282/2018"],
    ["Općinski građanski sud u Zagrebu", "P-785/2020"],
    ["Općinski kazneni sud u Zagrebu", "K-1899/2019"],
    ["Općinski građanski sud u Zagrebu", "P-12107/2019"],
    ["Županijski sud u Osijeku", "K-Us-9/2019"],
    ["Županijski sud u Zagrebu", "K-Us-11/2018"],
    ["Općinski sud u Splitu", "Z-27820/20"],
    ["Općinski prekršajni sud u Zagrebu", "Pp-226/2020"],
    ["Općinski prekršajni sud u Zagrebu", "Pp-5211/2021"],
    ["Općinski prekršajni sud u Zagrebu", "Pp-5142/2021"],
    ["Upravni sud u Zagrebu", "Usl-348/20"],
    ["Općinski građanski sud u Zagrebu", "P-2931/2021"],
    ["Općinski građanski sud u Zagrebu", "Z-36742/2010"],
    ["Općinski građanski sud u Zagrebu", "Z-20259/2020"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-29964/2020"],
    ["Općinski građanski sud u Zagrebu", "Pn-3651/2017"],
    ["Općinski radni sud u Zagrebu", "Pr-1676/2015"],
    ["Općinski građanski sud u Zagrebu", "P-1037/2008"],
    ["Općinski sud u Kutini", "Pn-160/2019"],
    ["Trgovački sud u Zagrebu", "Povrv-912/2018"],
    ["Trgovački sud u Zagrebu", "P-1637/2019"],
    ["Trgovački sud u Zagrebu", "P-1560/19"],
    ["Trgovački sud u Zagrebu", "P-1318/2018"],
    ["Trgovački sud u Zagrebu", "P-1477/2018"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-58743/19"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-49863/2019"],
    ["Općinski sud u Sesvetama, ZK odjel", "Z-440/2014"],
    ["Općinski građanski sud u Zagrebu", "Pn-2545/2018"],
    ["Općinski sud u Splitu, ZK odjel Supetar", "Z-20786/2019"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-34316/2020"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-34825/12"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-55780/2013"],
    ["Općinski građanski sud u Zagrebu, ZK odjel", "Z-36302/2016"],
    ["Općinski građanski sud u Zagrebu", "Ovr-1465/2019"],
    ["Općinski građanski sud u Zagrebu", "P-766/2019"],
    ["Općinski građanski sud u Zagrebu", "Ovr-375/2020"],
    ["Općinski građanski sud u Zagrebu", "R1-555/2020"],
    ["Trgovački sud u Zagrebu", "R1-4/2021"],
    ["Općinski sud u Splitu", "P-4300/2020"],
    ["Općinski građanski sud u Zagrebu", "Z-52429/2019"],
    ["Općinski sud u Novom Zagrebu", "Z-22493/2019"],
    ["Općinski sud u Sesvetama", "Z-10836/2019"],
    ["Općinski sud u Sesvetama", "Z-10833/2019"],
    ["Općinski radni sud u Zagrebu", "Pr-3823/2021"],
    ["Općinski radni sud u Zagrebu", "Pr-3326/2021"],
    ["Općinski građanski sud u Zagrebu", "Z-5768/21"],
    ["Općinski radni sud u Zagrebu", "Pr-13210/2020"],
    ["Općinski radni sud u Zagrebu", "Pr-3936/2021-2"],
    ["Upravni sud u Zagrebu", "Usl-49/18"]
];



function eSudovi()
{
    $endpoint = "https://e-predmet.pravosudje.hr/api/";
    $qry = '{"query":"query{sudovi {id, sudNaziv}}"}';

    $headers = array();
    $headers[] = 'Content-Type: application/json';

    $ch = curl_init();
    $agent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)';

    curl_setopt($ch, CURLOPT_USERAGENT, $agent);
    curl_setopt($ch, CURLOPT_URL, $endpoint);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $qry);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);

    $result = curl_exec($ch);

    curl_close($ch);
    return json_decode($result, true);
}


$eSudovi = eSudovi()["data"]["sudovi"];

function findSudCode($val, $eSudovi)
{
    foreach ($eSudovi  as $key => $value) {
        if ($value["sudNaziv"] == $val) {
            return $value["id"];
        }
    }
}



class ePredmeti
{
    public $epredmet;
    private $curl, $ini_opt;

    function __construct()
    {
        $endpoint   = 'https://e-predmet.pravosudje.hr/api/';
        $headers    = ['Content-Type: application/json'];
        $timeout = 30;

        $agent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)';

        $this->curl = curl_init();
        $this->ini_opt = [
            CURLOPT_USERAGENT => $agent,
            CURLOPT_URL => $endpoint,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_POST => true,
            CURLOPT_HTTPHEADER => $headers,
            CURLOPT_CONNECTTIMEOUT => $timeout,
            CURLOPT_TIMEOUT => $timeout
        ];
    }

    public function _exec($sud, $pred)
    {
        $start = microtime(true);

        $query_opt = [
            CURLOPT_POSTFIELDS =>
            '{"query":"query{ prvi:predmet(sud: ' . $sud . ', oznakaBroj: \"' . $pred . '\") {lastUpdateTime}}"}'
        ];
        curl_reset($this->curl);
        curl_setopt_array($this->curl, $this->ini_opt);
        curl_setopt_array($this->curl, $query_opt);
        $ret = curl_exec($this->curl);

        if (!curl_errno($this->curl)) {
            if (curl_getinfo($this->curl, CURLINFO_HTTP_CODE) !== 200) {
                echo 'HTTP error: ' . $http_code . '<br>';
                $this->epredmet = null;
            } else {
                $this->epredmet = json_decode($ret, true);
            }
        } else {
            echo curl_error($this->curl) . '<br>';
            $this->epredmet = null;
        }

        echo 'Took: ' . (microtime(true) - $start) . '<br>';
    }
}



$mycurl = new ePredmeti();

foreach ($arr as $value) {

    $sudCode = findSudCode(trim($value[0]), $eSudovi);
    $sudBroj = trim($value[1]);

    if (!empty($sudCode) && !empty($sudBroj) && isset($sudCode) && isset($sudBroj)) {
        // echo $sudCode . "<br>";
        // echo $sudBroj . "<br>";
        $mycurl->_exec($sudCode, $sudBroj);

        // var_dump($mycurl->epredmet);
        // echo "<br>";

        if (isset($mycurl->epredmet["data"]["prvi"]) && !empty($mycurl->epredmet["data"]["prvi"])) {

            if (isset($mycurl->epredmet["data"]["prvi"]["lastUpdateTime"]) && !empty($mycurl->epredmet["data"]["prvi"]["lastUpdateTime"])) {
                $lastUpdateTime = $mycurl->epredmet["data"]["prvi"]["lastUpdateTime"];
                $dateTime  = str_replace("T", " ", $lastUpdateTime);
                echo $dateTime . "<br>";
            }
        }
    }
}

This testing script is a bit faster for some reason but still times varies from 0.01 to 5, 6 sometimes 7 sec. And in production I still get 504 Gateway Time-out on one DB.

Im afraid to do this on large number of records...

Any other suggestions welcomed.

EDIT:

It just got to my attention that this GraphQL APi does support multiple cases in one request, it just needs alias in front. So my problems are solved...

\$\endgroup\$
0
2
\$\begingroup\$

Tip: reusing the curl handle keeps the connection to the server open. Since you are marking repeated request against the same host, this should speed up things. Use $ch = curl_init(); at the start of your code, out of the ePredmeti function in order to reuse your curl instance.

But you are not saying what part of the code is slow. We cannot test it, or you should provide a public URL. I suggest that you add a few prints in your code and inside the functions, to spit out a timestamp and you will see where the bottlenecks are.

Could be as simple as:

echo(date('d/m/Y H:i:s', time()));

Then you can figure out which part needs optimizations.

The database query itself also could be taking a long time, because it is not optimized, and very likely does not take advantage of table indexes (if they even exist). You should measure the average time of that query separately, and use the query plan (use the explain command in Mysql) to try to make this query perform better, if you find that is indeed taking a long time to run. It mainly depends if you have lots of records in your table or a moderate number.

It is also possible that the website applies some form of rate limiting and slowing you down. You should spoof the user agent to a mainstream browser (use CURLOPT_USERAGENT), because it is obvious to the website that your script is a bot. They can decide to penalize you and throttle traffic.

\$\endgroup\$
3
  • 1
    \$\begingroup\$ Please include that !empty() && isset() is an antipattern that shouldn't exist in anyone's application for any reason. stackoverflow.com/a/4559976/2943403 \$\endgroup\$ Jun 29 '21 at 23:34
  • \$\begingroup\$ I have edited my question with test code and did address some points come up in this post. Thanks for answering. \$\endgroup\$
    – ikiK
    Jun 29 '21 at 23:47
  • \$\begingroup\$ It just got to my attention that this GraphQL APi does support multiple cases in one request, it just needs alias in front. So my problems are solved... \$\endgroup\$
    – ikiK
    Jun 30 '21 at 9:59

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.