TClientDataSet and large insert

Question

In my application, I use TADOQuery with select (MSSQL) and its associated TClientDataSet. I need to insert about a million entries and ApplyUpdates.

So what do I see in SQL Server Profiler? I see that for each inserted row we have 3 queries: sp_prepare insert script, sp_execute with some values ​​and sp_unprepare.

I want to just prepare sql once for all records before inserting and after that update it. How can i do this?

Added after :

In the request, I have a script to execute the stored procedure:

tmpQuery := DefineQuery(FConnection, [
  'exec up_getOperatorDataSet ',
  '  @tablename     = :tablename, ',
  '  @operator      = :operator, ',
  '  @forappend     = :forappend, ',
  '  @withlinksonly = :withlinksonly, ',
  '  @ids           = :ids '
], [
  Param(ftString, sTableName),
  Param(ftInteger, FOperatorId),
  Param(ftBoolean, opForAppendOnly in OpenParams),
  Param(ftBoolean, opOnlyWithModelLinks in OpenParams),
  Param(ftString, sIds)
], Result);

It selects all fields from the sTableName table with some parameters.

An example of an insert from a profiler:

step 1:

declare @p1 int
set @p1=486
exec sp_prepare @p1 output,N'@P1 int,@P2 int,@P3 datetime,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 varchar(128),@P10 bit,@P11 numeric(19,4),@P12 smallint,@P13 smallint,@P14 smallint,@P15 smallint',N'insert into parser_prices
  (operator_id, request_id, date, nights, model_hotel_id, model_meal_id, model_room_id, model_htplace_id, spo, hotelstop, price, frout_econom, frout_business, frback_econom, frback_business)
values
  (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15)
',1
select @p1

Step 2:

exec sp_execute 486,21,2000450,'2009-12-04 00:00:00',14,2118,22,-9555,18,'2009-10.MSK.Bali.13.10.09-27.03.10',0,15530.0000,3,3,3,3

Step 3:

exec sp_unprepare 486

.

Answer 1

, TADOConnection. MSDASQL SQLOLEDB, , - .

Answer 2

, , SQL Server , . , .

, , , ​​, SQL :

{Prepare the insert query}
ADOQuery1.SQL.Append('INSERT INTO Tablename');
ADOQuery1.SQL.Append('(StringField1, IntField2)');             {repeat as necessary}
ADOQuery1.SQL.Append('VALUES (:sFieldValue1, :sFieldValue2)'); {repeat as necessary}
ADOQuery1.SQL.Prepare;

{In a For, While, Repeat loop, use:}
ADOQuery1.ParamByName('sFieldValue1').AsString := 'Value for field 1';
ADOQuery1.ParamByName('sFieldValue2').AsInteger := 2;
ADOQuery1.ExecSQL;

, ADOQuery, Delphi, TADO, - TDataSet.

Answer 3

...

• . , . .

• , . (, ) 256 ( , 4k).

• " " ( 2), , 10 000, 100 .

Answer 4

, , , TClientDataSet, , ( ).

, , , Borland, , , .

, TClientDataSet , . - , , .

, ClientDataSet . , , . , TClientDataSet, , .

( SQLite3 , ClientDataSet , ).