Application Code Redesign to reduce no. of Database Hits from Performance Perspective

Question

Scenario

I want to parse a large CSV file and inserts data into the database, csv file has approximately 100K rows of data.

Currently I am using fgetcsv to parse through the file row by row and insert data into Database and so right now I am hitting database for each line of data present in csv file so currently database hit count is 100K which is not good from performance point of view.

Current Code:

public function initiateInserts()
{
    //Open Large CSV File(min 100K rows) for parsing.
    $this->fin = fopen($file,'r') or die('Cannot open file');

    //Parsing Large CSV file to get data and initiate insertion into schema.
    while (($data=fgetcsv($this->fin,5000,";"))!==FALSE)
    {
        $query = "INSERT INTO dt_table (id, code, connectid, connectcode) 
                 VALUES (:id, :code, :connectid, :connectcode)";

        $stmt = $this->prepare($query);
        // Then, for each line : bind the parameters
        $stmt->bindValue(':id', $data[0], PDO::PARAM_INT);
        $stmt->bindValue(':code', $data[1], PDO::PARAM_INT);
        $stmt->bindValue(':connectid', $data[2], PDO::PARAM_INT);
        $stmt->bindValue(':connectcode', $data[3], PDO::PARAM_INT);

        // Execute the statement
        $stmt->execute();
        $this->checkForErrors($stmt);
    }
}

I am looking for a way wherein instead of hitting Database for every row of data, I can prepare the query and than hit it once and populate Database with the inserts.

Any Suggestions !!!

Note: This is the exact sample code that I am using but CSV file has more no. of field and not only id, code, connectid and connectcode but I wanted to make sure that I am able to explain the logic and so have used this sample code here.

Thanks !!!

Answer 1

I cannot offer the solution since i dont do PHP.

But you can store those strings in one array and send data to the BD in bulks of 100 or so.

Something like Conn.Execute(assembledInsert) where assembledInsert is the ToString of the array of inserts.

Keep a connection open in the whole process and use only this one.

Answer 2

on your while loop only build query string, and execute the statement out of loop. So something like this should work(not sure about syntax since its been a long time i have written php but it should work:

public function initiateInserts()
{
    //Open Large CSV File(min 100K rows) for parsing.
    $this->fin = fopen($file,'r') or die('Cannot open file');

    //Parsing Large CSV file to get data and initiate insertion into schema.
    $query = "";
    while (($data=fgetcsv($this->fin,5000,";"))!==FALSE)
    {
        $query = $query . "INSERT INTO dt_table (id, code, connectid, connectcode) 
                 VALUES (" . $data[0] . ", " . $data[1] . ", " . $data[2] . ", " . $data[3] . ")";
    }
     $stmt = $this->prepare($query);
     // Execute the statement
     $stmt->execute();
     $this->checkForErrors($stmt);
}

Answer 3

I think the point of prepared statements - besides security in query building - is "prepare once, execute many": prepared statements are meant to be run multiple times.

So you can try and take the prepare statement out of the loop and keep only parameter binding and execution in it.