ansaurus

Question

Why is this loop over mysql resultset slow? (1.4ms per cycle)

Answer 1

+2 A:

Big arrays take more time to allocate memory and handle. that's why we always ask a database to do all the job and return 10 rows as a final result.

Col. Shrapnel 2010-05-26 09:36:52

I thought that was just because we were **lazy**

Matthew Scharley 2010-05-26 09:39:04

Thanks. No way to speed it up? It's not a web app but a small php deamon messing with a lot of data. So I don't mind to wait but a minute at 100% cpu is a bit extreme and the resultset will grow by a factor of 10 minimum.

pawpro 2010-05-26 09:39:47

Do whatever you can in the database. It will *always* be faster than code you write. And you get the added bonus of less code you need to write.

Matthew Scharley 2010-05-26 09:40:55

Can you break the data up and do it in smaller work units?

Richard 2010-05-26 09:41:38

I.E. from what you said one could extrapolate that the way to get the desired 30 times boost would be to just break the array down to smaller ones...

pawpro 2010-05-26 09:42:31

@pawpro from what we have said, you have to use a database to do the calculations on this data

Col. Shrapnel 2010-05-26 10:18:53

Answer 2

A:

I suspect that what's taking all the time is the continual access to 4-dimensional arrays, where some (or all?) of the dimensions are being keyed from string fields, those values themselves having to be extracted from $row...

I would suggest you seriously reconsider:

whether you need all that in memory
if so, what the best data structure would be for optimal access

Alnitak 2010-05-26 09:42:11

Answer 3

+1 A:

Chris Smith 2010-05-26 09:44:17

Thanks. I do actually need random data from the database at high rates so I hoped not to have to put strain on the db as I might have continous bursts of data to process at rates higher than 500 q/s and I cannot currently afford to have such unpredictible load on the db. I'll play with it more worst case I'll try memcached :)

pawpro 2010-05-26 09:56:36

Answer 4

A:

What about this:

$cache = array();
while($row = $res->fetch_assoc())
{
    $key = $row['upload_id']."\n".$row['dialcode_id']."\n".$row['carrier_id'];
    $key1 = "1\n$key";
    if (!array_key_exists($key1, $cache))
      $cache[$key1] = &$clist[$row['upload_id']][$row['dialcode_id']][$row['carrier_id']];

    $ref = &$cache[$key1];
    $ref['std'] = $row['cost_std'];
    $ref['ecn'] = $row['cost_ecn'];
    $ref['wnd'] = $row['cost_wnd'];

    $key2 = "2\n$key";
    if (!array_key_exists($key2, $cache))
      $cache[$key2] = &$dialcode_destination[$row['upload_id']][$row['carrier_id']][$row['dialcode_id']]

    $ref = &$cache[$key2];
    $ref['other_destination']   = $row['destination_id'];
    $ref['carrier_destination'] = $row['carrier_destination_id'];
}

Tomalak 2010-05-26 09:59:00

I see where you going with this...nice I'll give memcached a shot first :)

pawpro 2010-05-26 10:10:18

@pawpro: memcached will not speed up the act of indexing into an array itself. Since you do many equal lookups in a loop, even a memcached-based solution would benefit from this approach.

Tomalak 2010-05-26 10:30:29

I do relize but I expect only <1% of this data to be in regular use rest will be "on demand from DB". So it is reasonable to cache it using memcached (db query cache would result in high cache miss rate as the table changes - but not the existing data)

pawpro 2010-05-26 10:48:20

ansaurus

tags:

views:

answers:

Why is this loop over mysql resultset slow? (1.4ms per cycle)

related questions