0

how can I improve the following code to import a CSV file of 75,000 data faster. What I am doing is comparing data from the csv with the data from mysql.

<?php
require('config.php');
$tipo       = $_FILES['dataCliente']['type'];
$tamanio    = $_FILES['dataCliente']['size'];
$archivotmp = $_FILES['dataCliente']['tmp_name'];
$lineas     = file($archivotmp);
$i = 0;
foreach ($lineas as $linea) {
    $cantidad_registros = count($lineas);
    $cantidad_regist_agregados =  ($cantidad_registros - 1);
    
    if ($i != 0) {
    $datos = explode("|", $linea);
       
    $codigo_inmueble              = !empty($datos[0])  ? ($datos[0]) : '';
    $ruta                    = !empty($datos[1])  ? ($datos[1]) : '';

       
    if( !empty($codigo_inmueble) ){
        $sqlVerificarExistencia = ("SELECT codigo_inmueble FROM serviciosglforjson WHERE codigo_inmueble='".($codigo_inmueble)."' ");
        $queryDuplicidad        = mysqli_query($con, $sqlVerificarExistencia);
        $cantidadDuplicidad     = mysqli_num_rows($queryDuplicidad);

if ( $cantidadDuplicidad == 0 ) { 
    

    $insertarRegistro = ("INSERT INTO serviciosglforjson(ruta) 
    SELECT ruta
    FROM serviciosglforjson WHERE codigo_inmueble = '$codigo_inmueble';");
    mysqli_query($con, $insertarRegistro);
        
} else{
    $updateData =  ("UPDATE serviciosglforjson SET 
        ruta='" .$ruta. "'
        WHERE codigo_inmueble='".$codigo_inmueble."'
    ");
    $resultadoUpdate = mysqli_query($con, $updateData);
    } 
  } //Cierre de mi 2 If
  } //Cierre de mi 1 If


      echo '<center><div>'. $i. "). " .$linea.'</div></center>';
    $i++;
}
  echo '<center><p style="text-aling:center; color:#333;">Total de Registros: '. $cantidad_regist_agregados .'</p></center>';
echo "<center><a href='index.php'>Atras</a></center>";
?>

For now it takes more than 2 hours to import. I need your help, please. I need to use another method to be able to improve the code and that the import of data in the CSV is fast.

tadman
  • 208,517
  • 23
  • 234
  • 262
Roger Gerber Quirino
  • 19
  • 2
  • How many records has `serviciosglforjson` table? – Robert Jan 14 '23 at 09:07
  • 1
    If the table `serviciosglforjson` is safe from other people editing it while you are running this you should be able to make a single query and cache the results in a local array which you query instead. You can also batch your insert and update statements, running 5, 10, 50, etc. at a time in a single query. Or you could import the whole file into a new temporary table and do your insert/update with just SQL – Chris Haas Jan 14 '23 at 11:20
  • 1
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Jan 14 '23 at 12:11
  • Yes, the serviciosglforjson table has 75,000 records, I want to import the records that are in the CSV, and while it imports it, it checks if the record exists, if it exists it updates it, otherwise it doesn't update it. – Roger Gerber Quirino Jan 14 '23 at 22:32
  • It works very well, but the import takes a long time, I want to improve the code so that it doesn't take too long. – Roger Gerber Quirino Jan 14 '23 at 22:34
  • Thank you, I was able to solve it, it takes less time using transactions. – Roger Gerber Quirino Jan 15 '23 at 02:11
  • It is not necessary to add "solved" to the title. Stack Overflow flags questions automatically. – tadman Jan 15 '23 at 02:17

0 Answers0