0

I have to regularly put 40k records into my database which is done in a big loop. This creates a single transaction for every SQL statement and can be quite slow.

In other languages like PHP, I can do transactions and commit all the data to the database in one shot. It speeds things up tremendously.

But I recently had to write a processing engine for my data in C, and I have not found a way to do transactions in this language. I tried turning off autocommit, then executing each query in the loop, and finally committing the data when the loop is complete. But the write is still taking almost 4 minutes. WAAAAYYYYY too long!

I don't find any information in the MySQL Connector/C documentation for transactions, but since they exist for other MySQL interfaces, surely there is something for C. Any ideas???

Drew
  • 24,851
  • 10
  • 43
  • 78
sax
  • 337
  • 1
  • 14
  • Well I wrap everything in stored procs for that jazz. It is a query: `call uspGenQtr(3,4,0,'Houston');` ... and in that proc, there is a `START TRANSACTION` and `COMMIT`, and you call the bloody thing like [Here](http://stackoverflow.com/a/39422295) – Drew Sep 12 '16 at 05:03
  • I don't know if it will work yet, but I am going to try the following that I found [here](http://stackoverflow.com/questions/11526369/c-sample-transaction-for-mysql) as well as this documentation which is for C++ but which I think that I can make work in C. It is similar to what Drew suggested [documentation](http://dev.mysql.com/tech-resources/articles/mysql-connector-cpp.html#trx) – sax Sep 12 '16 at 20:55
  • No No no ! Do a stored proc bro, have in it `start transaction`, then a big block of commands if you want, and a `commit` in the proc. It makes life so much easier. Just try it once. Your link is client side. Mine is all baked into the proc. Look at the proc `uspGetNextSequence`. You could have a `rollback` as part of an if then else. – Drew Sep 12 '16 at 20:59
  • OK. I'll look into it, but I am new to C and don't even know what a stored proc is or uspGetNextSequence. My program pulls 40k records from a table, processes the data, and then writes it back to a different table. If a stored proc is something that you can do on the fly like this, and is not something that you have to spend time creating, I will truly look into. I have to do this 550 times in a day, and at 4 minutes per write, there are not enough hours in the day :) So I must find a solution. – sax Sep 12 '16 at 21:09
  • come to the [Campaigns](http://chat.stackoverflow.com/rooms/95290) room. I will write a proc for you whenever you want. Just ping me with an `@` sign. – Drew Sep 12 '16 at 21:13
  • Looking at the link that you provided, aren't you doing this in SQL and not in C? – sax Sep 12 '16 at 21:19
  • Exactly. And then in C you just do a call to a normal query the way you always would like `select n from t` but in your case the string would be `call uspMyProcName(....)` . And that same proc could be called from Java or PHP or anywhere. – Drew Sep 12 '16 at 21:21
  • You seem to know a lot about big data and databases. Why would a stored proc be faster than sending a query to "START TRANSACTION", looping through a bunch of execute statements, and then sending a "COMMIT" query? Isn't that essentially the same thing, or is there something about a stored proc that gives you even bigger time savings? – sax Sep 12 '16 at 21:26
  • Drew, one last question. I need this database table to be very fast for a user, so I have indexed 11 colums in the table. I know that you pay a price when you want to INSERT or UPDATE a table with indexes, I just didn't think that it would be so substantial, going from about 11 seconds to 4 minutes, which is why I was looking into transactions. Does adding 40k records to a table with 11 indexes taking almost 4 minutes sound about right, or do you think that a stored proc could make it go much faster than that? – sax Sep 13 '16 at 19:41
  • I am going to delete some comments of mine here. There is a chat room link up there mentioned – Drew Sep 13 '16 at 20:01
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/123286/discussion-between-sax-and-drew). – sax Sep 13 '16 at 20:06

0 Answers0