Re: Inserts so slow using _Connection.Execute

Hi,
The process is two part. The first which is timed just using
Start := Now;
FromRecordSet.Open(s,FromConnection,adOpenKeySet,adLockOptimistic,adCmdText);
sb1.panels[2].text := 'Time : 'formatdatetime('ss.zzz',Start-Now);
This gives me about 1.05 seconds for the whole lot (If stuff is still coming
down the pipe then so be it, at this stage I dont need to dispaly it all
just about 100 records to show the user a sample. He can then easily say Yes
this is the data I want or No this is wrong.)
The second part, I now have to massage this data and feed it into another
sql box different server different database. I have now tried to do the
following as per Viatcheslav's suggestion

Com := TADOCommand.Create;
Com.CommandText := 'Insert [' + tablename + '] values
(:t1,:t2,:t3,:t4,:t5,:t6,:t7,:t8,:t9,:t10,:t11)';
while not........................
for col := .....................
s := ...some stuff
Com.Parameters[col].value := s;
end;
Com.Execute
end;

This now takes 0:43.98. While this is a great improvement, can anything
further be done to improve perfomance? Perhaps in some of the [adKeyconst]
which I confess I dont know too much about.

thanks for the help
steve


"Steve Zimmelman" <skz@xxxxxxxxxxxxxxxxxx> wrote in message
news:442bdba2@xxxxxxxxxxxxxxxxxxxxxxxxx
You said you are getting all 50K records in about 0.5 seconds? Probably
not. What's happening is you are getting the first part of the record set,
probably much smaller than you think. As you iterate through the record
set, ADO is actually fetching more records. So you have 2 executions
happening when you are adding records. Fetching and Updating.

Using the Insert Into method might off-load the process to the back-end
and reduce the amount of band-width used by individual Insert statements
for each record.

-Steve-

"Steve" <hughessatmdnationwide.co.uk> wrote in message
news:442b9423$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Actually my timer was wrong. the actual inserts are taking about 1
min.50 secs for the whole record set. This can not be the best that is
possible.

steve

"Steve Zimmelman" <skz@xxxxxxxxxxxxxxxxxx> wrote in message
news:442aac28$1@xxxxxxxxxxxxxxxxxxxxxxxxx
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: Inserts so slow using _Connection.Execute
    ... sql box different server different database. ... but sooo slow. ...
    (borland.public.delphi.database.ado)
  • Re: Database results weirdness
    ... changes are being made to the database and the new value is just text. ... FP_DumpError strErrorUrl, "Cannot create connection" ... FP_DumpError strErrorUrl, "Cannot create record set" ... server that supports Active Server Pages."); ...
    (microsoft.public.frontpage.programming)
  • Re: User list
    ... linked to separate applications on different users desktops. ... for me to tell who is logged into the database on the server? ... Steve S ...
    (microsoft.public.access.formscoding)
  • Re: Adding permissions to an AD user on a remote database
    ... I was looking at the second record set for "sp_helplogins" and it has ... not if it's a member of a database role. ... "Indicates whether the current user is a member of the specified Microsoft ... Windows group or Microsoft SQL Server database role. ...
    (microsoft.public.sqlserver.security)
  • ADO faster than ADO.net ?
    ... I would like to know why I can use ADO (ADODB record set) running in vb.net ... to insert records into an MS Access database 5-6 times faster than I can ... I have a small vb.net project that inserts 30,000 records into a database ...
    (microsoft.public.dotnet.framework.adonet)