We experience very slow ExecuteNonQuery execution times with long SQL command texts containing 1000s of INSERT statements. The command text has BEGIN TRANSACTION / COMMIT already.

Tested versions:

• NuGet: 1.0.116.0, 1.0.112.0
• .Net FW: 4.8, 4.6.1
• Windows: W10 x64 Enterprise, 21H2 19044.1826

I have the following SQL script file (exported from an SQL CE v4 SDF file, using the SqlCeScripting classes by ErikEJ):

BEGIN TRANSACTION;
CREATE TABLE [CacheItem] (
  [FileName] nvarchar(256) NOT NULL
, [Hierarchy] nvarchar(256) NOT NULL
, [Hash] binary(32) NULL
, [Operation] tinyint NULL
, [SubscriptionId] int NOT NULL
, [IsDirty] bit NOT NULL
, [IsNewlyArrived] bit NOT NULL
, [FolderId] uniqueidentifier NOT NULL
, [IsBackgroundProcessed] bit NOT NULL
, [DictationId] uniqueidentifier NOT NULL
, [BeginUploadStarted] datetime NULL
, CONSTRAINT [PK__CacheItem__0000000000000197] PRIMARY KEY ([DictationId])
);


-- 1st INSERT

INSERT INTO [CacheItem] ([FileName],[Hierarchy],[Hash],[Operation],[SubscriptionId],[IsDirty],[IsNewlyArrived],[FolderId],[IsBackgroundProcessed],[DictationId],[BeginUploadStarted]) VALUES (
'filename1.xml','Hier1',X'69D20F72CE4258341842BC5750877C10A0E60A55523BC7D12EA28F5B8E0FBB28',NULL,12345,0,1,'d6169967-4dfb-4913-bd72-6cb09518da48',0,'ef4567ab-ec93-4974-9f48-001713f57bbf',NULL);

-- In total, about 2700 INSERT statements

CREATE INDEX [idx_subsid_folderid] ON [CacheItem] ([SubscriptionId] ASC,[WebDriveFolderId] ASC);
COMMIT;

-- End of SQL script

To sum up the script:

• have a BEGIN TRANSACTION
• CREATE TABLE statement
• a lot of INSERTs
• a CREATE INDEX
• a COMMIT TRANSACTION

What we experience (same machine, same Windows user):

Case1:

• executing the entire script using SQLiteCommand.ExecuteNonQuery()
• Runtime: cca. 16_000 millisecs (16 seconds)

Case2:

• creating the table
• starting a transaction (SQLiteConnection.BeginTransaction)
• executing the INSERTs one-by-one
• commit transaction
• Runtime: cca 40-60 millisecs

Case3:

• sqlite3.exe newDB.sqlite < source.sqlscript
• Runtime: very fast, well below 100 millisecs

Any idea what we could do to achieve acceptable speeds for Case1?