Re: Poor performance inserting records into a table

[Brian Nickels]

I did some more digging and found the execute routine in the MySQL_Cursor.CPP file. It looks like the buffer is being sent to the MySQL server one byte at a time. See the section just below the "send the query" comment.The routine is shown below:

/*
execute_query - execute a query

This method sends the query string to the server and waits for a
response. If the result is a result set, it returns true, if it is
an error, it processes the error packet and prints the error via
Serial.print(). If it is an Ok packet, it parses the packet and
returns false.

query_len[in] Number of bytes in the query string

Returns boolean - true = result set available,
false = no result set returned.
*/
boolean MySQL_Cursor::execute_query(int query_len)
{
conn->store_int(&conn->buffer[0], query_len+1, 3);
conn->buffer[3] = byte(0x00);
conn->buffer[4] = byte(0x03); // command packet

// Send the query
for (int c = 0; c < query_len+5; c++)
conn->client->write(conn->buffer[c]);

// Read a response packet and check it for Ok or Error.
conn->read_packet();
int res = conn->check_ok_packet();
if (res == MYSQL_ERROR_PACKET) {
conn->parse_error_packet();
return false;
} else if (!res) {
return false;
}
// Not an Ok packet, so we now have the result set to process.
columns_read = false;
return true;
}

I'm not that great a C++ programmer, just a beginner - still learning. What would be the best way to set this up so the ESP-8266/Arduino transmits either the whole query or if too long, breaks it up into strings? I believe with a standard TCP packet, no jumbos, I can send around 1400 bytes in a packet. That would greatly increase performance, lower network traffic and improve efficiency.