ansaurus

Question

Speed up this code - PHP/SQL - Short Code but takes incredibly long at the moment

Answer 1

+1 A:

You can do a number of things.

on duplicate key instead of select
prepared statement if you have access to PDO MySQL.
stored procedure in MySQL

Try This. I'm unable to test it but the syntax should be right.

$counter = 0;
$events = sizeof($feed_array['1']) - 1;
while($counter <= $events )
{
  $eventDate = explode("/", $feed_array['1'][$counter]); //print_r($eventDate);
  $eventTime = explode(":", $feed_array['2'][$counter]); //print_r($eventTime);             
  $eventUnixTime = mktime($eventTime[0], $eventTime[1], "00", $eventDate[1], $eventDate[0], $eventDate[2]);

  $data = array(
    'eventtime'     => $eventUnixTime,
    'eventname'     => addslashes($feed_array['3'][$counter]),
    'venuename'     => addslashes($feed_array['4'][$counter]),
    'venueregion'   => addslashes($feed_array['5'][$counter]),
    'venuepostcode' => addslashes($feed_array['6'][$counter]),
    'country'       => addslashes($feed_array['7'][$counter]),
    'minprice'      => addslashes($feed_array['8'][$counter]),
    'available'     => addslashes($feed_array['9'][$counter]),
    'link'          => addslashes($feed_array['10'][$counter]),
    'eventtype'     => addslashes($feed_array['11'][$counter]),
    'seaOnSaleDate' => addslashes($feed_array['12'][$counter]),
    'perOnSaleDate' => addslashes($feed_array['13'][$counter]),
    'soldOut'       => addslashes($feed_array['14'][$counter]),
    'eventImageURL' => addslashes($feed_array['15'][$counter]),
    'perfID'        => addslashes($feed_array['16'][$counter]),
  );

  $update = array();
  foreach ($data as $key => $value)
    $update[] = "`$key` = '$value'";

  $sql = "INSERT INTO `dante_tickets`.`it_raw`" .
    '(`id`, `'. implode ('`,`', array_keys($data)) . '`) VALUES ' .
    '(NULL, ' . implode (',', $data) . ') ON DUPLICATE KEY UPDATE ' . 
    implode (',', $update);

  mysql_query($sql) or die(mysql_error().":".$sql);
  echo "Inserted or Updated".$feed_array['16'][$counter].": ".addslashes($feed_array['3'][$counter])."\n";

  unset($sql);
  $counter++;     
}

I forgot to mention this requires that perfID is a unique key.

gradbot 2009-07-11 21:41:04

Do you have any examples of using stored procedures. Also the PDO i'm not so sure about :/ i'll wait and see if something else comes up before attempting that.

Shadi Almosri 2009-07-11 21:47:06

Why do you believe a stored procedure would increase its performance? Can you cite an example with data which supports this?

MarkR 2009-07-11 21:48:42

@MarkR The stored procedure would turn his multiple queries into a single query. This alone would speed it up however using on duplicate key eliminates this.

gradbot 2009-07-11 22:01:13

so is ON DUPLICATE KEY defiantly faster?

Shadi Almosri 2009-07-11 22:11:40

cool - for now my method is quick enough - but your code is cleaner - so for the next revision i'll try it. Pity stack overflow doesn't have a "assisted answer" feature.

Shadi Almosri 2009-07-11 23:06:08

why not use REPLACE INTO or INSERT IGNORE INTO?

Werner 2009-07-12 10:38:03

Answer 2

+1 A:

You could try grouping inserts and updates into groups so the code runs less queries.

For example, you could group all of the inserts into one very large insert, or maybe group every 100 inserts.

Also using prepared statements as gradbot suggested may help.

Other than that, it's not very easy to say which part of it is the major contributor to slowness. You should use a profiler to determine that, for example by using a smaller dataset so the profiled script runs in a reasonable time.

Jani Hartikainen 2009-07-11 21:43:07

Answer 3

A:

Have you profiled this query?

"SELECT * FROM it_raw WHERE perfID = '".addslashes($feed_array['16'][$counter])."'"

Because you run it 28000 times.. so unless it is REALLY quick, it will cause you a head ache. Use the EXPLAIN syntax and add an appropriate index if needed.

EDIT: With profile, I mean that you should try to use EXPLAIN on the mysql-prompt to see what execution plan the MySQL Query Optimizer suggests for this query. I.e, on the prompt, run:
```
EXPLAIN SELECT * FROM it_raw WHERE perfID = 426; 
# Change this id to something existing and valid
```
What you want to see is that it is using an index, and ONLY an index. If you don't understand the output, copy and paste it here so can I go through it with you.

UPDATE: As you can see, it takes 0.07 seconds for EVERY row in your data array, plus the time to actually query the database, transfer the result etc. This is roughly 28000 * 0.07 = 1960 seconds, or 32 minutes, just to check if the data exists or not. You need to come up with another way of checking if the data already exists... One, very simple optimization might be:
```
EXPLAIN SELECT perfId FROM it_raw WHERE perfID = 210968;
```
This way, you can use the index on perfId and don't need to visit the table
If it is possible, try to avoid quering the database for each run in the loop. Perhaps it is possible to fetch the ids from the database into a big array of ids that will fit in PHP memory? This will be MUCH quicker than quering the database for every row in your big data array.

PatrikAkerstrand 2009-07-11 21:52:19

Can you explain what you mean by profiled? (echo micro time before and after?)

Shadi Almosri 2009-07-11 21:59:19

added an explain output (although using phpmyadmin - not sure if it effects what you see [i can use shell if needed]) in my question

Shadi Almosri 2009-07-11 22:11:08

Updated with shell out put and time...

Shadi Almosri 2009-07-11 22:17:37

No something isn't right with your update, as running that whole script with just the SELECT statements takes ~1min...

Shadi Almosri 2009-07-11 22:27:09

40seconds to be precise.

Shadi Almosri 2009-07-11 22:28:40

Answer 4

A:

This is exactly the scenario for which prepared statements were made:

$prepared_statement =
    $DB->prepare('INSERT INTO table(column, column) VALUES(?, ?)');
loop {
    $prepared_statement->execute(array('value1', 'value2');
}

It's implemented in the MySQLi and PDO wrappers. It only compiles the query once and automagically sanitizes the given data, saving time (both developmental and executional) and headache.

orlandu63 2009-07-11 23:02:11

ansaurus

tags:

views:

answers:

Speed up this code - PHP/SQL - Short Code but takes incredibly long at the moment

related questions