469,110 Members | 1,957 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

Transactions with remote connection

A colleague wants to insert many millions of records where the
values are computed in a C++ program. He connects to the
database with ODBC, and does an INSERT for each row.

This is slow, apparently because each INSERT is a separate
transaction. Is there a way to delay committing the data
until several thousand records have been written? Inside
SQL Server this is simple, but I don't see an equivalent
when using ODBC. Or is there something better than ODBC?
Or might it be faster to write values to a file and then
use bulk insert? I would appreciate any thoughts on this
general problem!

Thanks,
Jim
Jul 20 '05 #1
1 1821
Jim Geissman (ji**********@countrywide.com) writes:
A colleague wants to insert many millions of records where the
values are computed in a C++ program. He connects to the
database with ODBC, and does an INSERT for each row.

This is slow, apparently because each INSERT is a separate
transaction. Is there a way to delay committing the data
until several thousand records have been written? Inside
SQL Server this is simple, but I don't see an equivalent
when using ODBC. Or is there something better than ODBC?
Or might it be faster to write values to a file and then
use bulk insert? I would appreciate any thoughts on this
general problem!


You can send a BEGIN TRANSACTION statement from ODBC as well.

Furthermore he can gain even more speed by putting the INSERT statement
into a stored procedure, so SQL Server does have to parse and
optimize it each time. He should then be careful to use an RPC
mechanism to call the procedure, and not send an EXEC statment.
Unfortunately, I cannot give an example, as I have little experience
of ODBC programming myself.

An even speedier possibility is to use the bulk-copy interface.
Again, I can't give examples, because I only used the bulk-copy
interface in DB-Library. But some study of the manaul can be rewarding.

And the possibilty the fastest method is to build an XML document of
the lot, pass this to a stored procedure which uses
sp_xml_preprare_document and OPENXML to produce a result set which
you insert into the table in one single INSERT statement. (Or build
several documents of 100000 rows if you like.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Alban Hertroys | last post: by
6 posts views Thread by Christopher J. Bottaro | last post: by
2 posts views Thread by Adnan | last post: by
3 posts views Thread by Ryan H | last post: by
5 posts views Thread by Mitya Mitriev | last post: by
12 posts views Thread by Rami | last post: by
1 post views Thread by Mechanic | last post: by
reply views Thread by Mechanic | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.