How do I execute multiple mysqli_multi_queries?
I am trying to read a large XML file and INSERT all data into a database. I am testing 3 different scenarios (and I need help with the third):
- After each XML element, I make one request:
$this->mysqli->query($sql);
- I add the whole variable
INSERT
to$sql
, and after the loop I use one multiprocessor$this->mysqli->multi_query($sql);
to INSERT all at once.
I ran tests and I don't like both scenarios:
- Inserts all data into db but runs within 113 seconds. It's not a big problem, but I want to do better!
- This is the option I use for my small XML files, and it works wonders. For one of my functions, this reduced the execution time from 6.5s to 0.17 and exactly the same way. The problem with this file is this: this $ sql package is getting too big and the script becomes volatile, the output is wrong.
So now I came up with a third, hybrid solution. But it doesn't work as expected. The idea behind this is to create a call $this->mysqli->muli_query($sql);
after every 200 items and then restart the process. I think I am missing a correct understanding of the mysqli-> multi_query function because the code below makes sense to me. But maybe I need to free / point / next () something?
Anyway: After running the script, the database is not completely full.
As stated below my code:
if ($xmlObj) {
$sql = "";
$i = 1;
foreach ($xmlObj->Response->FeaturesList->Feature as $feature) {
$sid = $feature["ID"];
$sql .= "INSERT INTO ...;";
// Hybrid solution
if (($i % 200) == 0) {
$this->mysqli->multi_query($sql);
$sql = "";
}
$i++;
}
// Hybrid: Update the leftovers
$this->mysqli->multi_query($sql);
}
UPDATE
Option # 4 added by Mihai works great. I created a line INSERT..VALUES(a),(b),(c)
and added that using mysqli->query()
Runs 10 seconds and adds all items. Thanks to
The mystery question of multi_query remains though!
source to share
No one has answered this question yet
See similar questions:
or similar: