Board index » delphi » Inserting multiple Records into remote database with Stored Procedure

Inserting multiple Records into remote database with Stored Procedure

We have to regularly insert a group of 300 lines into a table on a remote
SQL Server database (connected via a 64k diginet line).  We have tried to do
this with a loop with 300 iterations where each iteration populates the
parameters of a ADO Stored procedure and then executes that procedure each
time. This is far too slow (2 sec plus per line) because the overhead of
refreshing the parameters each time and then executing the Stored procedure
take too long for each line.

Can anybody please help us with some advice how to do this better/faster. I
have heard that we could pass a table as a parameter but dont have a clue
how to do it. This could cut down on the repetitive connect/disconnect that
eats up time.

Any help will be appreciated.

Thanx
Christo

 

Re:Inserting multiple Records into remote database with Stored Procedure


If you want to execute a batch operation on client side records, you may
want to consider another tier in your application.

Upload your records to a MIDAS server and request the inserts be done there.

You will have to stay connected whilst the records upload and the batch
process begins, but after that you will be able to disconnect provided the
batch process works independently of your connection process.

If you have to deal with failed inserts then you need more code than this..

Mike.

--
Michael Collier Bsc (Hons) Comp Sci
ADO Support Tools.
Fully Working 15-Day Trial...
http://www.adoanywhere.com/download/aaBrowse2_7.zip
Home Page
http://www.adoanywhere.com

Quote
"Christo Bothma" <chri...@internet.co.za> wrote in message

news:3c56b83a_2@dnews...
Quote
> We have to regularly insert a group of 300 lines into a table on a remote
> SQL Server database (connected via a 64k diginet line).  We have tried to
do
> this with a loop with 300 iterations where each iteration populates the
> parameters of a ADO Stored procedure and then executes that procedure each
> time. This is far too slow (2 sec plus per line) because the overhead of
> refreshing the parameters each time and then executing the Stored
procedure
> take too long for each line.

> Can anybody please help us with some advice how to do this better/faster.
I
> have heard that we could pass a table as a parameter but dont have a clue
> how to do it. This could cut down on the repetitive connect/disconnect
that
> eats up time.

> Any help will be appreciated.

> Thanx
> Christo

Other Threads