Re: Inserts so slow using _Connection.Execute

I'm sorry, but I don't understand this. Could you give some more example
please. Also, we probably will not be allowed to go for additional 3rd party
libraries. So I need to do this with available type libraries or VCL
components.

regards
steve

"Viatcheslav V. Vassiliev" <support@xxxxxxxxxxxxxxx> wrote in message
news:442ab531$1@xxxxxxxxxxxxxxxxxxxxxxxxx
You create INSERT statement on each iteration. Use prepared TADOCommand
and set command text to

insert into <yourtable> (<col1>, <col2>, <col3>) values (?, ?, ?)

and inside cycle set parameters. 50 000 records is not much.

For fastest inserts you may try bulk inserts using IRowsetFastLoad, this
feature is available in OLEDB Direct (http://www.oledbdirect.com), look
example SQLServer\Insert 1000000 rows, it inserts 1M rows (simple table)
in 10 sec.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Steve" <hughessatmdnationwide.co.uk> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: 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
    ... and inside cycle set parameters. ... feature is available in OLEDB Direct, ... http://www.oledbdirect.com - The fastest way to access MS SQL Server, ... sooo slow. ...
    (borland.public.delphi.database.ado)
  • Re: One Web Service updates SQL, the other cant
    ... Here is the dialog between Tom and I. ... Columnist, SQL Server Professional ... "Steve Ricketts" wrote in message ... I guess its because this is my first Web Service and I> just used the access methods I knew. ...
    (microsoft.public.vb.database.ado)
  • Re: is it possible to re-build index without logging?
    ... Microsoft SQL Server Storage Engine ... "Steve Lin" wrote in message ... >> Andrew J. Kelly SQL MVP ... >>> the log file run out of space. ...
    (microsoft.public.sqlserver.server)
  • Re: Indexed Views
    ... Columnist, SQL Server Professional ... "Steve Z" wrote:> Pg 443 of Inside MS SQL Server 2000... ... > The first index you must build on a view is a clustered index, and since the> clestered index contains all the data at its leaf level, this index actually> does materialize the view. ...
    (microsoft.public.sqlserver.server)
  • Re: us date format
    ... >>> I don't know the context where Steve recommended using CLng, ... I don't disagree with this statement: I believe SQL Server uses a different ... there's no reason why you can't use this "trick" to set the ...
    (microsoft.public.access.formscoding)