Need more optimization

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Need more optimization

Post 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.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
rehfeld
Forum Regular
Posts: 741
Joined: Mon Oct 18, 2004 8:14 pm

Post 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 .= ",";
        }
    }
}


?>
rehfeld
Forum Regular
Posts: 741
Joined: Mon Oct 18, 2004 8:14 pm

Post by rehfeld »

god damn kettle_drums too fast lol
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post 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;
        }
rehfeld
Forum Regular
Posts: 741
Joined: Mon Oct 18, 2004 8:14 pm

Post by rehfeld »

Code: Select all

<?php

// got 10% here too


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


?>
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

lol. Sorry...i was stalking the boards :P
rehfeld
Forum Regular
Posts: 741
Joined: Mon Oct 18, 2004 8:14 pm

Post by rehfeld »

lol
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post 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 :)
Post Reply