I was looking at some code from a client where solution records are stored in a MySQL database. They use standard SQL INSERT statements to do this. Even for small number of solution records this is quite slow. I suggest a few simple improvements. E.g. MySQL has an interesting SQL INSERT extension where you can add multiple VALUEs to a single INSERT statement, like:
INSERT INTO mytable (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9)
In addition I surrounded the INSERTs by a transaction, which will increase the performance even more (there will be only one commit). For the fastest performance a bulk insert can be done using the LOAD DATA INFILE statement which can load a text file directly into a table.
For n=1,000 solution records I saw:
|Method||Time for 1,000 solution points||Note|
|(1) INSERT||41 seconds||this was the original setup: 12,000 inserts|
|(2) INSERT multiple values||7 seconds||2,000 inserts|
|(2)+transactions||1.2 seconds||2,000 inserts|
|LOAD DATA INFILE||0.3 seconds||2 statements to load 2 csv files|
Some optimization models generate a ton of solution results, and in those cases it may be useful to spend a little bit of time on deciding how to store solutions in a database.