Poor performance inserting records into a table
I am running the Arduino version of the MySQL connector and noticed it took 13 seconds to post an insert statement into a MySQL table. The insert statement is:
INSERT INTO weather_vers1.sensor_reading (measurement, reading, unit_of_measure, sensor_id, metric) VALUES ('T',73.9,'F',3237464,0),('H',47.4,'RH',3237464,0),('P',28.87,'HG',3237464,0),('D',52.6,'F',3237464,0)
I ran a network trace and found out the ESP8266 is sending the insert statement one character at a time. Below is a chopped down version of the trace. Notice the payload length is 1 for all ESP to MySQL conversations while the final response from the server is 53 bytes acknowledging that 4 records were inserted.
SEQ Offset Source Destination Prot Payload
1374 25.9254442 ESP-8266 MySql Server TCP PayloadLen=1,
1377 25.9767419 MySql Server ESP-8266 TCP PayloadLen=0,
1378 25.9789536 ESP-8266 MySql Server TCP PayloadLen=1,
1379 26.0391143 MySql Server ESP-8266 TCP PayloadLen=0,
1382 26.0411178 ESP-8266 MySql Server TCP PayloadLen=1,
1383 26.0420829 MySql Server ESP-8266 TCP PayloadLen=53,
The relevant portions of my code are:
char* INSERT_SQL = "INSERT INTO weather_vers1.sensor_reading (measurement, reading, unit_of_measure, sensor_id, metric) VALUES ('%s',%s,'%s',%d,%d),('%s',%s,'%s',%d,%d),('%s',%s,'%s',%d,%d),('%s',%s,'%s',%d,%d)";
MySQL_Connection conn((Client *)&client);
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
Serial.print("Connection Status: ");
Serial.println(conn.connected());
}
else
Serial.println("Connection failed.");
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
sprintf(query, INSERT_SQL, "T", tempStr, "F", id, 0, "H", humStr, "RH", id, 0, "P", presStr, "HG", id, 0, "D", dewPointStr, "F", id, 0);
cur_mem->execute(query);
delete cur_mem;
My question becomes, Is this how the connector is suppose to work or am I doing something wrong?