· 7 years ago · Sep 03, 2018, 07:40 PM
1How can I improve my INSERT statement performance?
2ny, nx, nz = np.shape(data)
3query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s)"""
4for frames in range(nz):
5 for rows in range(ny):
6 for cols in range(nx):
7 cursor.execute(query, (frames, rows, cols, data[rows,cols,frames]))
8
9query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
10values = []
11for frames in range(nz):
12 for rows in range(ny):
13 for cols in range(nx):
14 if data[rows,cols,frames] > 0.0:
15 values.append((frames, rows, cols, data[rows,cols,frames]))
16cur.executemany(query, values)
17
18query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
19values = []
20rows, cols, frames = numpy.nonzero(data)
21for row, col, frame in zip(rows, cols, frames):
22 values.append((frame, row, col, data[row,col,frame]))
23
24cur.executemany(query, values)
25
26query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
27rows, cols, frames = numpy.nonzero(data)
28values = [(row, col, frame, val) for row, col, frame, val in zip(rows, cols, frames, data[rows,cols,frames])]
29cur.executemany(query, values)
30
31patient_name, sample_date dd/mm/yyyy, frame_time (ms), frame 0..248, row 0..255, col 0..62, value
32"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,5,39,0.4
33"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,5,40,0.4
34...
35"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,10,42,0.4
36"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,10,43,0.4
37"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,4,40,0.4
38"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,5,39,0.4
39"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,5,40,0.7
40"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,6,44,0.7
41"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,6,45,0.4
42...
43"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,10,0.4
44"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,11,0.4
45"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,12,1.1
46"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,13,1.4
47"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,14,0.4
48
49drop table if exists sample_temp;
50create table sample_temp
51(
52patient_name varchar(255) not null,
53sample_date date,
54frame_time decimal(6,2) not null default 0,
55frame_id tinyint unsigned not null,
56row_id tinyint unsigned not null,
57col_id tinyint unsigned not null,
58value decimal(4,1) not null default 0,
59primary key (frame_id, row_id, col_id)
60)
61engine=innodb;
62
63truncate table sample_temp;
64
65start transaction;
66
67load data infile 'c:\import\frames.dat'
68into table sample_temp
69fields terminated by ',' optionally enclosed by '"'
70lines terminated by 'rn'
71ignore 1 lines
72(
73patient_name,
74@sample_date,
75frame_time,
76frame_id,
77row_id,
78col_id,
79value
80)
81set
82sample_date = str_to_date(@sample_date,'%d/%m/%Y');
83
84commit;
85
86Query OK, 24799 rows affected (1.87 sec)
87Records: 24799 Deleted: 0 Skipped: 0 Warnings: 0
88
89INSERT INTO data
90 (frame, sensor_row, sensor_col, value)
91VALUES
92 (1, 1, 1, 1),
93 (2, 2, 2, 2),
94 (3, 3, 3, 3),
95 ...
96
97values = [(frames, rows, cols, data[rows,cols,frames])
98 for frames in range(nz) for rows in range(ny)
99 for cols in range(nx) if data[rows,cols,frames] > 0.0]