Re: Inserts so slow using _Connection.Execute

Thanks for the input.
If I use bcp, don't I have to save the records to a text file first? I don't
really want to do this for security reasons, and other considerations.
DTS is the usual method of doing this sort of thing, but I need to have a
user do some of the selecting of data first.
regards
steve


"Arnie" <none> wrote in message news:442aaed9$1@xxxxxxxxxxxxxxxxxxxxxxxxx
"Steve" <hughessatmdnationwide.co.uk> wrote in message
news:442a5bad$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Can any one help here? I have the following job to do - which works but
sooo slow.
D7 - SQL 2K to different databases on 2 different servers.
I connect to the first server / database using _Recordset and _Connection
and pull back 50000 records in about .5 sec. This is fine. I then need to
do some work on this record set and then insert these records into a
different database on a different server. I use another _Connection to do
the following
While Not FromRecordSet.EOf do
begin
// process the records

end
Instr := 'Insert ['+tablename+'] values (' + stemp + ')';
ToConnection.Execute(Instr,recs,-1)
FromRecordSet.MoveNext
end;// While not eof
The insert takes about 30 seconds to do this with 50000 records. This is
way to slow. I could get the same performance using TTAbles. I want this
application to be a console app when it's finished so don't need any
fancy gui stuff.

any help would be appreciated.
regards
steve

You might want to take a look at SQL Server's BCP API. It's used for bulk
loading. Simply put, you create an array of records which contain your
data. Then you tell it to 'execute' for 50,000 rows. It is quite fast.
It uses a C API.

You might also want to look at DTS. Check out Books Online.

- Arnie



.