title: MySQL C# performance of Insert
body: <p>I am developering a application which checks the database on startup (updates for new data) and when work is done(on shutdown/log-off) it pushes the performance logs to the database. The users themself is not changeing any data, they are only generating logs (the money comes from their use of the data ;)</p>
  
  <p>When the users is done with the work, the application pushes logs to the database(MySQL database), I do not want to constantly push data, due to the connections are expected to drop and go offline doing the work day (mobile work), the less time online the better. This means the application have to be able to work in offline mode too. </p>
  
  <p>The log pushed for a single user is usually about 2000 records, and each record contains about 70 bytes of data. 
  There is about 100 user at peak time (may grow to 300 in the near future) which makes it about 200.000 records of logs which is pushed to the MySQL Database each day. Because the users work at the same hours, there is going to be heavy peak times. Worst case is 200.000 records each of 70 bytes at the same time(~14 mb of data). </p>
  
  <p>The database I am using is a MySQL database, this is choosen mostly because:</p>
  
  <ul>
  <li>It is free (Sells arguments)</li>
  <li>I can find help online</li>
  <li>It is a standard database, means other IT Dept most likely know about it already</li>
  </ul>
  
  <p>I am developing the application using: C# .Net 4.5</p>
  
  <p>I have tried to use EntityFramework, this is very easy to start with, but it kinda fails on preformance. 
  The 2000 logs (inserts) for a single user takes about 7 seconds when I run the program + Server on my developer machine. And 7 seconds is unacceptable (moreover it will proberbly increase dramaticly when 200 users are doing it at the same time)
  As I have read about it, it appears EntityFramework makes every insert as a single SQL command, and take one SQL Command at a time. </p>
  
  <p>So I have tried to use MySQL Connector/Net. But I do not want to do it like EntityFramework, and do each insert as a single command. So my eyes went to MySqlBulkLoader. But it only want to accept a file, and not raw data, is there a way to load MySqlBulkLoader with data within the program, I would prefer not to save data to the harddisk to be able to send data to the database, it feels like a unnecessary detour. </p>
  
  <p><strong>So my questions is(no more story telling ;)</strong></p>
  
  <ul>
  <li>Can I load MySqlBulkLoader with data from memory without creating a file on the disk?</li>
  <li>Should I use MySQL Connector/Net or is there another way I should do it (like raw SQL statements)? </li>
  </ul>
  
  <p><strong>EDIT: THE ANSWER IS</strong></p>
  
  <p>Use MySQL Connector/Net with raw SQL commands, make the insert as a Batch Insert <a href="https://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql">LIKE THIS</a>.</p>