SqliteVec BuildInsertCommand is slow as it does not reuse a single command for inserts

Start in MEVD/src/SqliteVec/SqliteCommandBuilder.cs, especially the cited lines around BuildInsertCommand and its parameter construction. Trace how insert commands are built and executed, then verify that the command text and parameter bindings remain reusable across records while key-returning and non-returning paths behave as expected.

BuildInsertCommand constructs a chained multi-row INSERT with record-index-suffixed parameter names for every batch, so the CommandText and parameter names change with every call and Microsoft.Data.Sqlite cannot reuse any prepared statement, re-preparing all statements and allocating all parameters per batch.

Steps to reproduce

1. Create a collection with a string key, one data property and a 256-dimension vector.
2. Upsert batches of 200 records repeatedly and profile, or simply inspect the DbCommand produced by a batch.
3. Observe the CommandText: Rather than one insert statement that is reused for each record, it is multiple statements, one statement per record, with parameter names like @Content13 embedding the record index, and it differs for every batch.

Expected behavior

The SQL text for an insert is stable across records so the provider's per-command prepared statement cache and parameter bindings can be reused, and only values are re-bound per record.

Actual behavior

Every batch builds a multi-kilobyte CommandText and a large set of SqliteParameter objects. For a 200-record batch 200 statements are added to the CommandText.

Analysis

The connector builds one INSERT per record inside a loop over the records, and derives parameter names from the record index:

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/SqliteCommandBuilder.cs#L131-L138

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/SqliteCommandBuilder.cs#L228-L235

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/SqliteCommandBuilder.cs#L237-L237

https://github.com/CommunityToolkit/AI/blob/215a5bad3307aaafe45ba2ee9e40c071f5f7ce2c/MEVD/src/SqliteVec/SqliteCommandBuilder.cs#L557-L558

Microsoft.Data.Sqlite caches prepared statements on the SqliteCommand instance and reuses them only while the CommandText is unchanged. Because the generated text embeds per-record parameter names and grows with the batch, no reuse across records is possible.

Additionally the data insert always runs through ExecuteReaderAsync even when no database-generated key needs to be read back, adding a reader allocation and result-set handling per batch.

Cause

Statement preparation cost scales with the number of records per batch, per operation, because the command is both rebuilt per operation and structured so each record has its own uniquely named parameters.

Possible fix

Build one fixed single-row INSERT command per table per operation (INSERT OR REPLACE INTO "t" (...) VALUES (@Key, @Content, ...)), prepare it once, and loop over the records re-binding parameter values only. Keep a RETURNING variant only for the database-generated int or long key case, and use ExecuteNonQueryAsync otherwise.

Environment

• OS: Windows 11 and Linux
• Package: CommunityToolkit.VectorData.SqliteVec 1.0.1-preview
• Microsoft.Data.Sqlite 10.x, sqlite-vec 0.1.7-alpha.2.1