Re: Inserts so slow using _Connection.Execute

Hi Steve,

I've never had to insert 50,000 records, so I can't speak to the time factor.
But I'm not sure if you are saying it takes 30 seconds per inserted record, or
30 seconds for the entire 50,000? If it's taking 30 seconds to insert 50,000
records, that doesn't seem like it's too far off.

However, another approach might be to do something like:

// Get the source 50,000 records
Select *
From SourceDB..SourceTable
Where <SomeCondition>

// work on the records and save them back.

// Move them into the targe table
Insert Into OtherDB..TargetTable
Select * SourceDB..SourceTable
Where <SomeCondition>

This assumes that the same user name and password can be used to access both
DB's.

-Steve-

"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




.

Relevant Pages

  • Re: Trolls and sock puppets
    ... I know.....Low IQ troll and a stalking troll! ... What are you wining by saying things like this? ... forge an identity to speak to themselves, or to deceive others just to win ... Does it really matter? ...
    (rec.music.classical.guitar)
  • Re: IF JG Wins
    ... someone speaks, proper English and Southern drawl and all, is not very ... they sound or speak, is that at the end of the day - and forgive that ... I get exactly what you're saying. ...
    (rec.autos.sport.nascar)
  • Re: Trolls and sock puppets
    ... I know.....Low IQ troll and a stalking troll! ... What are you wining by saying things like this? ... forge an identity to speak to themselves, or to deceive others just to win ... Does it really matter? ...
    (rec.music.classical.guitar)
  • Re: Cindy Sheehan, rational person
    ... >> Tom Enright was cut from the Baylor football team for saying... ... > you wouldn't speak for my fallen brethren. ... Sheehan and on behalf of Cindy Sheehan. ...
    (rec.sport.football.college)
  • Re: First Avoid Coding
    ... I speak of techniques. ... If you speak of techniques there's a good chance I'll understand what ... Either it's saying that all problems are easily solved. ... Swimming is a test of the swimmer not the water (Lynne Cox talent ...
    (comp.object)