title: How can I improve my INSERT statement performance?
body: <p><a href="https://stackoverflow.com/questions/3738269/how-to-insert-arrays-into-a-database/3738402#3738402">While Josh's answer here</a> gave me a good head start on how to insert a 256x64x250 value array into a MySQL database. When I actually tried his INSERT statement on my data it turned out horribly slow (as in 6 minutes for a 16Mb file).</p>
  
  <pre><code>ny, nx, nz = np.shape(data)
  query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s)"""
  for frames in range(nz):
      for rows in range(ny):
          for cols in range(nx):
              cursor.execute(query, (frames, rows, cols, data[rows,cols,frames]))
  </code></pre>
  
  <p>I was reading <a href="https://rads.stackoverflow.com/amzn/click/com/1849510180" rel="nofollow noreferrer" rel="nofollow noreferrer">MySQL for Python</a>, which explained that this wasn't the right approach because executing 4 million separate inserts is very inefficient.</p>
  
  <p>Now my data consist out of a lot of zeros (more than 90% actually), so I threw in an IF statement so I only insert values greater than zero and I used <strong>executemany()</strong> instead:</p>
  
  <pre><code>query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
  values = []
  for frames in range(nz):
      for rows in range(ny):
          for cols in range(nx):
              if data[rows,cols,frames] &gt; 0.0:
                  values.append((frames, rows, cols, data[rows,cols,frames]))           
  cur.executemany(query, values)
  </code></pre>
  
  <p>This miraculously brought my processing time down to about 20 seconds, of which 14 seconds spend on creating the list of <strong>values</strong> (37k rows) and 4 seconds on the actual inserting into the database.</p>
  
  <p><strong>So now I'm wondering, how can I speed up this process any further?</strong> Because I have a feeling my loop is horribly inefficient and there has to be a better way. If I need to insert 30 measurements per dog, this would still take 10 minutes, which seems far too long for this amount of data.</p>
  
  <p>Here are two versions of my raw files: <a href="http://dl.dropbox.com/u/5207386/rolloff" rel="nofollow noreferrer">with headers</a> or <a href="http://dl.dropbox.com/u/5207386/sel_1" rel="nofollow noreferrer">without headers</a>. I'd love to try the LOAD DATA INFILE, but I can't figure out how to parse the data correctly.</p>