0

I am new to node-mysql but I am having a speed issue that is worrysome because of how simple it is. I am working with a fairly reasonable amount of data (2000 mysql entries) and entering it via connection.query() has become very slow when I use multiple queries. The code is this 

var rows = xlsx.rows;
for (var i=0; i<rows.length; ++i) {
    var escaped = '';
    var values = [];
    var row = rows[i];
    escaped += 'INSERT INTO Main (name, value, enabled) VALUES ';

    var name = row['name'];
    var val = row['value'];
    var enabled = rows['enabled'];

    escaped += '(?,?,?);';
    values.push(name);
    values.push(val);
    values.push(enabled);

    connection.query(escaped, values);
}

It takes upwards of one minute to input all the rows. The same issue has arisen when I use a multiple statements inside one query. The only time I am able to enter all the rows quickly, and almost instantly, is if I use one string and one entry, a.k.a. 

INSERT INTO Main (name, value, enabled) VALUES (?,?,?), (?,?,?), (?,?,?)...

Am I just using the queries in an inefficient manner? Or is there an actual issue with the speed of the queries here?

andykais
  • 996
  • 2
  • 10
  • 27
  • With your current code the system (node as well as mysql again) have to parse the query 2000 times. If you rewrite your stuff to use the second query, this will just happen once. Also maybe helpful: http://stackoverflow.com/a/14259347/1169798 – Sirko Jun 23 '15 at 14:09
  • The issue is that this limits what I can do significantly. I want to insert foreign keys related to table Main but the keys are difficult to keep track of with one single insert – andykais Jun 23 '15 at 14:36

1 Answers1

0

As mentioned in the comments, this is just a slow way of inserting mysql data, it is much easier to use

connection.query('INSERT INTO Table (col1, col2) VALUES ?', [insert], callback);

where insert is a variable containing the multiple entry values

andykais
  • 996
  • 2
  • 10
  • 27