Our inserts are done through a C++ program, but I believe that a stored procedure serves as a single transactional unit (someone else may be able to verify this). I'm not sure how this would work. Our code makes sure the db connection is not in auto-commit mode, inserts the records, then commits the transaction.
Lock collisions certainly do occur during our inserts. We collected performance statistics using the db analyzer and it showed a great number of lock collisions and timeouts. We ended up increasing the number of locks in the kernel. The db analuzer is a great tool for figuring out which resources are being consumed.
Regards, Mark
Javier Gostling wrote:
On Mon, Nov 10, 2003 at 09:42:08AM -0600, Mark Johnson wrote:
We did two things that significantly improved our insert performance. The first was putting a transaction around the entire insert process and committing the transaction at the end (this has been documented in this list several times before). The second was to perform the inserts in "bulk" mode - i.e., - we store the rows in an array and execute the insert as a single statement (usually in "chunks" of 250,500, or more). This is performed through the ODBC API calls and some internally developed libraries, so I don't know if this method is appropriate for your application, but it made a world of difference in ours.
[snip] Regarding your first suggestion, should I move the sequence of inserts and updates that perform the message posting into a dbproc in order to surround it in a transaction? And another question that arises: wouldn't using a transaction here increase lock collisions?
Still, I will try out your suggestions and check performance.
Thanks you very much,
