471,893 Members | 1,274 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

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 1942
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
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.