StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you are using a separate transaction for each insert, that would definitely affect performance, as the DB server would have to atomically perform each insert. I have never used SQL server, but most SQL variants have a way to bunch more than one inserts in a single transaction, usually with something like</p> <pre><code>BEGIN TRANSACTION; ...<various SQL statements>... COMMIT TRANSACTION; </code></pre> <p>For the SQL server syntax see:</p> <p><a href="http://msdn.microsoft.com/en-us/library/ms188929.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms188929.aspx</a></p> <p><a href="http://msdn.microsoft.com/en-us/library/ms190295.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms190295.aspx</a></p> <p>In my experience bundling inserts like this definitely helps with server performance and, to some extent, resource and network usage.</p> <p>EDIT:</p> <p>Most (all?) decent DB servers use some sort of per-row locking, rather than per-table locks. You should be able to have multiple concurrent transactions, each with multiple inserts, with no problem - that's what DB servers are designed for. You could certainly have each worker thread perform its own transactions, thus parallelizing the inserts from different threads.</p> <p>Since you are apparently using a single computer for the computations and the DB, extensively parallelizing DB transactions would not affect performace too much and it might even make it worse, since you don't really have any network latencies to reduce the impact of. As long as all CPU cores are busy, which would probably imply a number of workers >= 12, you should be looking at other optimisations.</p> <p>If your threads generate their output in one go <em>after</em> processing (e.g. if you compute a large matrix and <em>then</em> dump in to the database) I doubt you would gain anything by storing the result into a file and then having the DB read it back into a table.</p> <p>If, on the other hand, your threads do their output piece-by-piece you might benefit by storing parts of their output in memory, then inserting those parts in the DB, performing more than one transactions per round. Raising the number of worker threads in that case might allow you to have better CPU utilisation while the DB is storing the data, <em>if</em> the CPU is underutilised.</p> <p>Storing the worker output in a file should IMHO be avoided since it effectively triples the load on the disk subsystem. The only reason you might want to do that is if you really don't have the memory for intermediate storing of the results.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload