How to commit inserts in a single statement

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
rbrodner
Posts: 8
Joined: Fri 11 Mar 2011 21:34
Location: Portland, Oregon, USA

How to commit inserts in a single statement

Post by rbrodner » Mon 14 May 2012 21:43

In SDAC, it appears that multiple inserts are sent as multiple statements, with each statement in its own round trip to the server, with its own GO. In ADO, batches are combined into a single statement. Is it possible to make SDAC do this as well?

Here are examples of inserts on the same table, one using TMSQuery, and one using TADODataset.

------------------------Using SDAC------------------------------

EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(1)', 'G1', 'g'
go
EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(2)', 'G2', 'gg'
go
EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(3)', 'G3', 'ggg'
go
EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(4)', 'G4', 'gggg'
go

------------------------Using ADO-------------------------------

EXEC sp_executesql N'INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P1,@P2);
INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P3,@P4);
INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P5,@P6);
INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P7,@P8)',
N'@P1 varchar(2),@P2 varchar(1),@P3 varchar(2),@P4 varchar(2),@P5 varchar(2),@P6 varchar(3),@P7 varchar(2),@P8 varchar(4)',
'H1', 'h', 'H2', 'hh', 'H3', 'hhh', 'H4', 'hhhh'

AndreyZ

Re: How to commit inserts in a single statement

Post by AndreyZ » Tue 15 May 2012 07:07

Hello,

To obtain the same functionality in SDAC, you should set the TMSQuery.CachedUpdates property to True and the TMSQuery.Options.UpdateBatchSize property to the number of commands that will be executed in a batch.

rbrodner
Posts: 8
Joined: Fri 11 Mar 2011 21:34
Location: Portland, Oregon, USA

Re: How to commit inserts in a single statement

Post by rbrodner » Wed 16 May 2012 16:37

Thanks - I looked in help but could not find the correct property. I notice that when I set this to a value > 1, my identity columns don't seem to update correctly after a commit. Is there something else I need to do to make them work correctly?

AndreyZ

Re: How to commit inserts in a single statement

Post by AndreyZ » Thu 17 May 2012 11:17

Thank you for the information. We reproduced the problem and the investigation is in progress. We will notify you when we have any results.

AndreyZ

Re: How to commit inserts in a single statement

Post by AndreyZ » Tue 09 Oct 2012 13:00

We have investigated this problem. Unfortunately, adding of such functionality is a very hard task. We will investigate the possibility of implementing this functionality but we cannot provide any time frame. ADO works when there are IDENTITY fields only because it stops to use batches and inserts records using multiple statements. You can achieve the same behaviour with SDAC by setting the TMSQuery.Options.UpdateBatchSize property to 1.

Post Reply