Batch Insert using Pdo and transaction

Question

Good morning guys I’m a little new in the world ai php mysql and I’m in need of a help

I am inserting data in my bd in 6 tables, data comes from txt files

some files have 5000 lines with 163 records separated by ;

Until then all right I can already insert the data in the tables

but files with more than 500 lines are taking long

I read and Mum site that gives to make the Inserts in blocks of hundred in hundred 400 in 400

using transaction just didn’t quite understand the concept of splitting blocks I’m making

with online transaction

follows the abstract scenario and a piece of code to see if it helps

tab1

tab2 - has foreign key for tab1

Tab3 - has foreign key for tab2

tab4 - has foreign key for Tab3

tab5 - has foreign key for Tab3

tab6 - has foreign key for Tab3

while ( ! feof ( $f ) ) {

// Ler uma linha do arquivo
$linha = fgetcsv ( $f, 0, $delimitador );
if (! $linha) {
    continue;
}



    $conn = $calculo->Connect ();
    $conn->beginTransaction ();

    $stmtOrgao = $conn->prepare("INSERT INTO orgao () values(null, ?,?)");
    $dadosOrgao = array (
            $linha [0],
            $linha [1]
    );
    $stmtOrgao->execute ( $dadosOrgao );
    $idOrgao = $conn->lastInsertId ();

    $stmtFuncionario = $conn->prepare("insert into funcionario() values(null,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                             ?,?,?,?,?,?,?,?,?,?,?,?,
                                                                             ?,?,?,?,?,?,?,?,?,?,?,?,
                                                                             ?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

    $dadosFuncionario = array(
        $linha[2],$linha[3],$linha[4],$linha[5],$linha[6],$linha[7],$linha[8],$linha[9],$linha[10],$linha[11],$linha[12],
        $linha[13],$linha[14],$linha[15],$linha[16],$linha[17],$linha[18],$linha[19],$linha[20],$linha[21],$linha[22],
        $linha[23],$linha[24],$linha[25],$linha[26],$linha[27],$linha[28],$linha[29],$linha[30],$linha[31],$linha[32],
        $linha[33],$linha[34],$linha[35],$linha[36],$linha[37],$linha[38],$linha[39],$linha[40],$linha[41],$linha[42],
        $linha[43],$linha[44],$linha[45],$linha[46],$linha[47],$linha[48],$linha[49],$linha[50],$idOrgao);                    

    $stmtFuncionario->execute($dadosFuncionario);
    $idFuncionario = $conn->lastInsertId();

    $stmtFolha = $conn->prepare("insert into folha () values(null,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                  ?,?,?,?,?,?,?,?,?,?,?,?)");



    if(preg_match("/[R$]/i", $linha[67]))
    {

        $separar = explode(" ", $linha[67]);
        $lin = $separar[1];
    }
    else
    {

        $lin = $linha[67];
    }



    $dadosFolha = array(
        $linha[51],$linha[52],$linha[53],$linha[54],$linha[55],$linha[56],$linha[57],$linha[58],$linha[59],$linha[60],$linha[61],
        $linha[62],$linha[63],$linha[64],$linha[65],$linha[66],$lin,$linha[68],$linha[117],$linha[118],$linha[119],$linha[120],
        $linha[121],$idFuncionario);

    $stmtFolha->execute($dadosFolha);
    $idFolha =  $conn->lastInsertId();

    $stmtProvento = $conn->prepare("insert into proventos () values(null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                          ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                          ?,?,?,?,?,?,?,?)");

    $dadosProvento = array(
        $linha[69],$linha[70],$linha[71],$linha[72],$linha[73],$linha[74],$linha[75],$linha[76],$linha[77],$linha[78],$linha[79],
        $linha[80],$linha[81],$linha[82],$linha[83],$linha[84],$linha[85],$linha[86],$linha[87],$linha[88],$linha[97],$linha[98],
        $linha[99],$linha[100],$linha[101],$linha[102],$linha[103],$linha[106],$linha[107],$linha[108],$linha[109],$linha[110],
        $linha[111],$linha[112],$linha[113],$linha[114],$linha[116],$idFolha);
    $stmtProvento->execute($dadosProvento);

    $stmtDesconto = $conn->prepare("insert into descontos () values(null, ?,?,?,?,?,?,?,?,?,?,?,?)");
    $dadosDesconto= array(
        $linha[89],$linha[90],$linha[91],$linha[92],$linha[93],$linha[94],
        $linha[95],$linha[96],$linha[104],$linha[105],$linha[115],$idFolha);
    $stmtDesconto->execute($dadosDesconto);

    $stmtOutros = $conn->prepare("insert into outros () values(null, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                     ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                                                                     ?,?)");

    $dadosOutros = array(
        $linha[122],$linha[123],$linha[124],$linha[125],$linha[126],$linha[127],$linha[128],$linha[129],$linha[130],$linha[131],
        $linha[132],$linha[133],$linha[134],$linha[135],$linha[136],$linha[137],$linha[138],$linha[139],$linha[140],$linha[141],
        $linha[142],$linha[143],$linha[144],$linha[145],$linha[146],$linha[147],$linha[148],$linha[149],$linha[150],$linha[151],
        $linha[152],$linha[153],$linha[154],$linha[155],$linha[156],$linha[157],$linha[158],$linha[159],$linha[160],$linha[161],
        $linha[162],$idFolha);

   $stmtOutros->execute($dadosOutros);

    $conn->commit();
    $conn=null;
}

Answer 1

In the case of your code I recommend you move the prepare codes before the while. This way you can go giving Insert in the database to each reading of the file, avoiding having a lot of variables in memory.

When you do a prepare, you ask the database to translate the query, mount the execution plan and keep it in the internal cache of the database.

To gain the desired performance, you must prepare the querys only once with the method prepare and then just go calling the method bind of each Pdostatement and the method execute.

You can also use the named placeholders bind method so that it doesn’t all have to be with queries. Example:

$stmtFunctioning = $Conn->prepare("Insert into funcionario() values(null,:campo1,:campo2,:campo3)");

Then when you are going to bind in the parameters you can do: $stmtFunctioning->bind(':field1', $valueCampo);