In my application I use TADOQuery with select (MSSQL) and linked with it TClientDataSet. I have to insert about million records and ApplyUpdates.
So what I see in the SQL Server Profiler? I see that for each inserted row we have 3 queries: sp_prepare of insert script, sp_execute it with some values and sp_unprepare.
I want just to prepare sql once for all of the records before insert and unprepare it after. How can I do it?
Added after:
In the query I have a script for the stored procedure execution:
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 of the fields from table sTableName with some parameters.
Example of inserting from 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
and it is for all of the new rows.
The answer was in provider used in TADOConnection. Switched from MSDASQL to SQLOLEDB and all is right now, without any additional queries.