Re: Inserts so slow using _Connection.Execute

"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


.

Relevant Pages

  • RE: Replication error, PLEASE HELP
    ... I have not checked the database yet but it is not so likely the couse becouse ... the same error that occured on the first server now has occured on one server ... But there in a production enviroment and I don´t want to disturb ... "Regina" wrote: ...
    (microsoft.public.sqlserver.replication)
  • Re: ESE97 184 - 1019 error
    ... first server with better hardware/drives and move the mailboxes back. ... Is it possible to create a new database on the existing drive and then ...
    (microsoft.public.exchange.admin)