MySQL Forums
Forum List  »  Connector/Arduino

Poor performance inserting records into a table
Posted by: Brian Nickels
Date: December 02, 2017 12:43AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Poor performance inserting records into a table
1519
December 02, 2017 12:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.