473,379 Members | 2,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 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 2017
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Alban Hertroys | last post by:
Oh no! It's me and transactions again :) I'm not really sure whether this is a limitation of psycopg or postgresql. When I use multiple cursors in a transaction, the records inserted at the...
6
by: Christopher J. Bottaro | last post by:
Hi, Why is there no support for explicit transactions in the DB API? I mean like transaction() to start the trans and commit() and rollback() would end the trans or something. The reason why I...
7
by: Richard Maher | last post by:
Hi, I am seeking the help of volunteers to test some software that I've developed which facilitates distributed two-phase commit transactions, encompassing any resource manager (e.g. SQL/Server...
2
by: Adnan | last post by:
Hey Ppl, I'm developing an Online Auction Site using ASP.net and am experiencing a problem with Transactions in ADO.Net. When beginTrasaction() function is invoked from a specific connection,...
3
by: Ryan H | last post by:
Hi, My data access layer for my VB.NET 1.1 application resides on a different server and is accessed via Remoting. Is there a way to configure my data access layer so that I can use...
5
by: Mitya Mitriev | last post by:
Hi I created ASP.NET 2.0 web application that uses SQL Server 2000 database and it works perfectly when website and Sql server were on local machine. Then I installed application on production...
2
by: Sridhar | last post by:
Hi, I am trying to implement sql transactions. But I am not knowing how to do that. I created a data access layer which contains methods to select/insert/update tables in a database. I have also...
12
by: Rami | last post by:
I have some requirement for an automated payment system. The system has four machines setup as follows: 1- Two machines have a clustered database. 2- Two machines have a .net business logic...
1
by: Mechanic | last post by:
I am trying to use Transactions in conjunction with Ajax and mutable pages. I am supposed to have the user complete the entire process before any data is committed to the database. Does anyone have...
0
by: Mechanic | last post by:
I am trying to use Transactions in conjunction with Ajax and mutable pages. I am supposed to have the user complete the entire process before any data is committed to the database. Does anyone have...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.