重新设计应用代码以减少数量。从性能角度分析数据库命中数 [英] Application Code Redesign to reduce no. of Database Hits from Performance Perspective

问题描述

我想解析一个大型CSV文件并将数据插入数据库中，csv文件包含大约10万行数据。

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

当前我正在使用 fgetcsv 逐行解析文件并将数据插入数据库，所以现在我正在为csv文件中存在的每一行数据命中数据库，因此当前数据库命中数为100K，这从性能角度来看并不理想。

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.

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.

任何建议!!!

注意：这是确切的示例代码我正在使用，但CSV文件没有更多信息。字段，不仅是ID，代码，connectid和connectcode，而且我想确保自己能够解释逻辑，因此在这里使用了此示例代码。

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.

谢谢!!!

推荐答案

仅在while循环上构建查询字符串，并执行循环外语句。所以这样的事情应该工作（不确定语法，因为我写了php很久了，但是它应该工作：

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);
}

这篇关于重新设计应用代码以减少数量。从性能角度分析数据库命中数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！