Page 1 of 1

Need more optimization

Posted: Mon Nov 29, 2004 7:56 pm
by Roja
Okay folks, I've got a chunk of code, and I need it ruthlessly optimized. It takes more time to run than 9 other *pages* of code in the same sequence - so it seriously does need it (no useless optimization here!).

To understand it a bit, we build an insert query to pass to the db, of up to (but not more than) 2,000 pairs of numbers. This (large) query is then passed to the db. Doing a large query of up to 2k pairs turned out to be DOZENS of times faster than doing each query individually.

Above this code, we build up an array of start/destination combo's. These start/destinations are somewhat random, but follow complex rules, so for the sake of argument, dont worry about the formation of insertquery[$s], other than to know its format.

Insertquery[1] looks like: "(11,22)"

Insertquery consists of anywhere from 0-2000 ($s) pairs. The pairs order does matter - it follows rules, so don't swap em.

$_POST['sektors'] is the total count of sectors - the default is 5,000, but we are working on getting it to scale to 100,000 and beyond.

The query has to END correctly - ie, the end of the call must NOT end in a comma, hence the if s!=1 comment adding a comma only if its not the very last line.

Code: Select all

if  (( ($i % 2000)==0 || ($i==$_POST['sektors'])) && ($s>0) && ($i>0))
{
    $query = "INSERT into {$db_prefix}links (link_start, link_dest) VALUES ";
    for (; $s>0; $s--)
    {
        $query = $query . $insertquery[$s-1];
        if ($s != 1)
        {
            $query = $query . ",";
        }
    }
}
This chunk of code according to the benchmarking, is taking the longest. Any help - even a tiny suggestion saving 0.5s for 5,000 sectors, is deeply appreciated.

In case more context is helpful, feel free to pull up the full page of code.

Posted: Mon Nov 29, 2004 8:15 pm
by kettle_drum
See what happens if you always add the comma to the end of the query and then remove this single comma at the end - should save a bit of time as you dont have to keep checking to see if its the last query.

Is there any difference if you do:

Code: Select all

$query .= $insertquery[$s-1];
You also want to remove 1 from $s in the statement and also in the array key shown above - can you not do --$s instead of $s-- in the for statement so that it happens before and so you dont have to repeat removing 1.

Posted: Mon Nov 29, 2004 8:21 pm
by rehfeld
ive got a few more things to try, but right away i saw these 2 oportunities

i changed how you were concatenating $query

i got about 20% speed boost from just that


Code: Select all

<?php

if  (( ($i % 2000)==0 || ($i==$_POST['sektors'])) && ($s>0) && ($i>0))
{
    $query = "INSERT into {$db_prefix}links (link_start, link_dest) VALUES ";
    for (; $s>0; $s--)
    {
        $query .= $insertquery[$s-1];
        if ($s != 1)
        {
            $query .= ",";
        }
    }
}


?>

Posted: Mon Nov 29, 2004 8:22 pm
by rehfeld
god damn kettle_drums too fast lol

Posted: Mon Nov 29, 2004 8:23 pm
by Roja
Another dev suggested this.. it trimmed it by half:

Code: Select all

if  (( ($i % 2000)==0 || ($i==$_POST['sektors'])) && ($s>0) && ($i>0))
        {
            $query = "INSERT into {$db_prefix}links (link_start, link_dest) VALUES ";

            $comma_added = implode(",", $insertquery);
            $query = $query . $comma_added;
            $s = 0;
        }

Posted: Mon Nov 29, 2004 8:24 pm
by rehfeld

Code: Select all

<?php

// got 10% here too


    while (--$s)
    {
        $query .= $insertquery[$s-1];
        if ($s !== 1)
        {
            $query .= ',';
        }
    }


?>

Posted: Mon Nov 29, 2004 8:24 pm
by kettle_drum
lol. Sorry...i was stalking the boards :P

Posted: Mon Nov 29, 2004 8:24 pm
by rehfeld
lol

Posted: Mon Nov 29, 2004 8:28 pm
by trukfixer
Roja wrote:Another dev suggested this.. it trimmed it by half:

Code: Select all

if  (( ($i % 2000)==0 || ($i==$_POST['sektors'])) && ($s>0) && ($i>0))
        {
            $query = "INSERT into {$db_prefix}links (link_start, link_dest) VALUES ";

            $comma_added = implode(",", $insertquery);
            $query = $query . $comma_added;
            $s = 0;
        }
hehehe glad it worked :)