interval data transfer to mysql dB

[kmf909]

Hi Doc

i try to transfer accelerometer data (LIS3DH) to MySQL on raspberry pi using complex insert sketch. it run smoothy but the interval of the data send to mysql seems too slow. based on timestamp from the table, the interval is 1 to 2 second for each data. i try to eliminate unnecessary delay but it is still the same. below is part of the sketch. could you appoint where i need to edit. thx

void loop() {

//lis.read(); // get X Y and Z data at once

sensors_event_t event; lis.getEvent(&event);

float y = event.acceleration.y; char msgy[50]; dtostrf(y, 5, 6, msgy); //convert axis y data to string

Serial.print(" \t "); Serial.print(event.acceleration.y); //Serial.println(); Serial.println(msgy); delay(10);

//delay(50);

Serial.println("Recording data.");

// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Save
sprintf(query, INSERT_DATA, msgy, 1);

// Execute the query
cur_mem->execute(query);

// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
Serial.println("Data recorded.");

}

[ChuckBell]

Hi,

Well, there is some turn around for the connection and you can save some time by removing the Serial calls but 1 second is probably going to be about right. The Arduino is not the fastest network device. In fact, I’ve found it to be very slow compared to PCs and other devices.

If you need more precision than that (I would ask myself why I wanted a faster sampling rate), then you may want to consider using bulk inserts - inserting several rows at one time. Think of it as a cache. But in this case, you would have to record the timestamp manually, which would be more work.

The other factor you should consider is your sensor - what is its sampling rate? Most are in the milliseconds so it shouldn’t be a problem but I have found some that need as much as 5-10 seconds between samples to settle/reset.

Dr. Bell

On Apr 5, 2017, at 1:28 AM, kmf909 notifications@github.com wrote:

Hi Doc

i try to transfer accelerometer data (LIS3DH) to MySQL on raspberry pi using complex insert sketch. it run smoothy but the interval of the data send to mysql seems too slow. based on timestamp from the table, the interval is 1 to 2 second for each data. i try to eliminate unnecessary delay but it is still the same. below is part of the sketch. could you appoint where i need to edit. thx

void loop() {

//lis.read(); // get X Y and Z data at once

sensors_event_t event; lis.getEvent(&event);

float y = event.acceleration.y; char msgy[50]; dtostrf(y, 5, 6, msgy); //convert axis y data to string

Serial.print(" \t "); Serial.print(event.acceleration.y); //Serial.println(); Serial.println(msgy); delay(10);

//delay(50);

Serial.println("Recording data.");

// Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Save sprintf(query, INSERT_DATA, msgy, 1);

// Execute the query cur_mem->execute(query);

// Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem; Serial.println("Data recorded."); }

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/14, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4LMy5wrO1YibW05u99dH6pSz5bX0ks5rsyaagaJpZM4MzxMg.