Geek Answers Handbook

How to handle a large mail request

Unfortunately, I can’t show you the code, but I can give you an idea of ​​what it looks like, what it does and what I have a problem ...

<?php
   include(db.php);
   include(tools.php);
   $c = new GetDB(); // Connection to DB
   $t = new Tools(); // Classes to clean, prevent XSS and others
   if(isset($_POST['var'])){
       $nv = json_decode($_POST['var'])

       foreach($nv as $k) {
          $id = $t->clean($k->id);
          // ... goes on for about 10 keys
          // this might seems redundant or insufficient
          $id = $c->real_escape_string($id);
          // ... goes on for the rest of keys...

          $q = $c->query("SELECT * FROM table WHERE id = '$id'");
          $r = $q->fetch_row();
          if ($r[1] > 0) {
          // Item exist in DB then just UPDATE
             $q1 = $c->query(UPDATE TABLE1);
             $q4 = $c->query(UPDATE TABLE2);
             if ($x == 1) {
                 $q2 = $c->query(SELECT);
                 $rq = $q2->fetch_row();
                 if ($rq[0] > 0) {
                     // Item already in table just update
                     $q3 = $c->query(UPDATE TABLE3);
                 } else  {
                     // Item not in table then INSERT
                     $q3 = $c->query(INSERT TABLE3);
                 }
             }
          } else {
            // Item not in DB then Insert
             $q1 = $c->query(INSERT TABLE1);
             $q4 = $c->query(INSERT TABLE2);
             $q3 = $c->query(INSERT TABLE4);
             if($x == 1) {
                $q5 = $c->query(INSERT TABLE3);
             }
          }
       }
   }

As you can see, these are very simple INSERT, UPDATE script tables, so before we get to the full version, we did some tests to see that the script works as it should, and the “result”, where it’s fine ...
Thus , we ran this code against 100 requests, everything when it’s just fine ... less than 1.7 seconds for 100 requests ... but then we saw the amount of data that needed to be sent / sent, it was a jaw shot for me .. . more than 20 thousand elements are required to send from 3 to 5 minutes, but the script always crashes "data" - this is an array in json

array (
   [0] => array (
             [id] => 1,
             [val2] => 1,
             [val3] => 1,
             [val4] => 1,
             [val5] => 1,
             [val6] => 1,
             [val7] => 1,
             [val8] => 1,
             [val8] => 1,
             [val9] => 1,
             [val10] => 1
         ),
   [1] => array (
             [id] => 2,
             [val2] => 2,
             [val3] => 2,
             [val4] => 2,
             [val5] => 2,
             [val6] => 2,
             [val7] => 2,
             [val8] => 2,
             [val8] => 2,
             [val9] => 2,
             [val10] => 2
         ),
//... about 10 to 20K depend on the day and time
)

json... , , , 3 5 - , , , ... , 503, , , -, VPS max_execution_time , , 10K +, 1 VPS, max_execution_time... , , 10K + , 1K , ... ... ... "" , , , 1K , , , ... ?

+4
2

, , , . zedfoxus . , . , PHP .. . :

  • . MySQL . .
  • , $c->real_escape_string(). , , $t->clean().

  • . , , PHP , MySQL SELECT UPDATE. MySQL . , , :

    . , ( ), . TABLE3. "" .

    . , id , . UPDATE. , . , insert (, ), .

  • MySQL, .

  • , INSERT, , , . INSERT , : MySQL

, , - , , .

:

<?php
   /* You can make sure that the connection type is persistent and 
    * I personally prefer using the PDO driver.
    */
   include(db.php);
   /* Definitely think twice about each tool that is included.
    * Only include what you need to evaluate the submitted data.
    */
   include(tools.php);
   $c = new GetDB(); // Connection to DB
   /* Take a look at optimizing the code in the Tools class. 
    * Avoid any and all kinds of loops–this code is going to be used in 
    * a loop and could easily turn into O(n^2) performance drain. 
    * Minimize the amount of string manipulation requests. 
    * Optimize regular expressions.
    */
   $t = new Tools(); // Classes to clean, prevent XSS and others
   if(isset($_POST['var'])){ // !empty() catches more cases than isset()
       $nv = json_decode($_POST['var'])

       /* LOOP LOGIC
        * Definitely test my hypothesis yourself, but this is similar 
        * to what I would try first.
        */

       //Row in database query
       $inTableSQL = "SELECT id FROM TABLE1 WHERE id IN("; //keep adding to it

       foreach ($nv as $k) {
           /* I would personally use specific methods per data type.
            * Here, I might use a type cast, plus valid int range check.
            */
           $id = $t->cleanId($k->id); //I would include a type cast: (int)
           // Similarly for other values
           //etc.
           // Then save validated data to the array(s)
           $data[$id] = array($values...);
           /* Now would also be a good time to add the id to the SELECT
            * statement
            */
           $inTableSQL .= "$id,";
       }

       $inTableSQL .= ");";

       // Execute query here

       // Then step through the query ids returned, perform UPDATEs,
       // remove the array element once UPDATE is done (use prepared statements)

       foreach (.....

       /* Then, insert the remaining rows all at once...
        * You'll have to step through the remaining array elements to
        * prepare the statement.
        */
       foreach(.....

   } //end initial POST data if


       /* Everything below here becomes irrelevant */

       foreach($nv as $k) {
          $id = $t->clean($k->id);
          // ... goes on for about 10 keys
          // this might seems redundant or insufficient
          $id = $c->real_escape_string($id);
          // ... goes on for the rest of keys...

          $q = $c->query("SELECT * FROM table WHERE id = '$id'");
          $r = $q->fetch_row();
          if ($r[1] > 0) {
          // Item exist in DB then just UPDATE
             $q1 = $c->query(UPDATE TABLE1);
             $q4 = $c->query(UPDATE TABLE2);
             if ($x == 1) {
                 $q2 = $c->query(SELECT);
                 $rq = $q2->fetch_row();
                 if ($rq[0] > 0) {
                     // Item already in table just update
                     $q3 = $c->query(UPDATE TABLE3);
                 } else  {
                     // Item not in table then INSERT
                     $q3 = $c->query(INSERT TABLE3);
                 }
             }
          } else {
            // Item not in DB then Insert
             $q1 = $c->query(INSERT TABLE1);
             $q4 = $c->query(INSERT TABLE2);
             $q3 = $c->query(INSERT TABLE4);
             if($x == 1) {
                $q5 = $c->query(INSERT TABLE3);
             }
          }
       }
   }
+1

. , , , . - :

include(db.php);
include(tools.php);
$c = new GetDB(); // Connection to DB
$t = new Tools(); // Classes to clean, prevent XSS and others
if(isset($_POST['var'])){
    $nv = json_decode($_POST['var'])

    $table1_data = array();
    $table2_data = array();
    $table3_data = array();
    $table4_data = array();

    foreach($nv as $k) {
        $id = $t->clean($k->id);
        // ... goes on for about 10 keys
        // this might seems redundant or insufficient
        $id = $c->real_escape_string($id);
        // ... goes on for the rest of keys...

        $table1_data[] = array( ... );
        $table2_data[] = array( ... );
        $table4_data[] = array( ... );
        if ($x == 1) {
            $table3_data[] = array( ... );
        }
    }

    $values = array_to_sql($table1_data);
    $c->query("INSERT INTO TABLE1 (...) VALUES $values ON DUPLICATE KEY UPDATE ...");
    $values = array_to_sql($table2_data);
    $c->query("INSERT INTO TABLE2 (...) VALUES $values ON DUPLICATE KEY UPDATE ...");
    $values = array_to_sql($table3_data);
    $c->query("INSERT INTO TABLE3 (...) VALUES $values ON DUPLICATE KEY UPDATE ...");
    $values = array_to_sql($table4_data);
    $c->query("INSERT IGNORE INTO TABLE4 (...) VALUES $values");
}

3 5 , 4 .

array_to_sql, , , . TABLE4 INSERT IGNORE , "" .

0

More articles:


All Articles