· 7 years ago · Sep 04, 2018, 05:18 AM
1PHP mySQL performance issues on large series of insert statements, should I use something else?
2private function toDB(){
3 $sql[] = "DROP TABLE IF EXISTS checklisttest";$sql[] = "CREATE TABLE checklisttest (
4 Incident varchar(12) NOT NULL,
5 TestID mediumint(9) NOT NULL AUTO_INCREMENT,
6 Element varchar(12) NOT NULL,
7 Name varchar(128) NOT NULL,
8 Code varchar(512) NOT NULL,
9 Expected varchar(512) NOT NULL,
10 Actual varchar(512) NOT NULL,
11 AutoVerifyResult varchar(32) NOT NULL,
12 QAResult varchar(32) DEFAULT NULL,
13 Comments text,
14 PRIMARY KEY (TestID)
15)";
16
17 //iterate through the records $this->records[10001] -- There can be anywhere from 100 - 300 records
18 foreach($this->records as $inc => $record){
19 //iterate through the element ids $this->records[10001][E02_04]
20 foreach($this->records[$inc]["Elements"] as $elementID => $element){
21 //iterate through the element ids $this->records[10001][E02_04][1] --There can be anywhere from 150 - 350 elements per record.
22 foreach($element as $key => $val){
23 $sql[] = "
24INSERT INTO `checklistTest` VALUES ("$inc",NULL,"$elementID","$val[name]","$val[code]","$val[expected]","$val[actual]","$val[match]","$val[QAResult]",NULL)";
25 }
26 }
27 }
28 foreach($sql as $key => $val){
29 mysql_select_db("new",$GLOBALS['local']);
30 mysql_query($val,$GLOBALS['local']) or die(mysql_error());
31 }
32 //echo "<textarea style='width:100%;height:400px'>$sql</textarea>";
33 //mysql_select_db("new",$GLOBALS['local']);
34 //mysql_query($sql,$GLOBALS['local']) or die(mysql_error());
35}
36
37//iterate through the records $this->records[10001] -- There can be anywhere from 100 - 300 records
38
39foreach($this->records as $inc => $record){
40 //iterate through the element ids $this->records[10001][E02_04]
41
42 foreach($this->records[$inc]["Elements"] as $elementID => $element){
43
44 //iterate through the element ids $this->records[10001][E02_04][1]--There can be anywhere from 150 - 350 elements per record.
45 foreach($element as $key => $val){
46 $sql.= "VALUES ("$inc",NULL,"$elementID","$val[name]","$val[code]","$val[expected]","$val[actual]","$val[match]","$val[QAResult]",NULL),";
47 }
48 }
49 }
50
51//echo "<textarea style='width:100%;height:400px'>$sql</textarea>";
52//mysql_select_db("new",$GLOBALS['local']);
53//mysql_query($sql,$GLOBALS['local']) or die(mysql_error());
54
55$pdo->beginTransaction();
56$statement = $pdo->prepare('
57 INSERT INTO checklistTest
58 VALUES(?, NULL, ?, ?, ?, ?, ?, ?, ?, NULL)
59');
60foreach($this->records as $inc => $record){
61 foreach($this->records[$inc]["Elements"] as $elementID => $element){
62 foreach($element as $key => $val) {
63 $statement->execute(array(
64 $inc,
65 $elementID,
66 $val['name'],
67 $val['code'],
68 $val['expected'],
69 $val['actual'],
70 $val['match'],
71 $val['QAResult']
72 ));
73 }
74 }
75}
76$pdo->commit();
77
78CREATE TABLE checklistTest (
79 ... // No changes inside
80) ENGINE=InnoDB