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

[rossdonald]

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:

AI/MEVD/src/SqliteVec/SqliteCommandBuilder.cs

Lines 131 to 138 in 215a5ba

	foreach (var record in records)
	{
	var isRecordKeyDatabaseGenerated = isKeyPossiblyDatabaseGenerated
	? (keyProperty.Type == typeof(int) && keyProperty.GetValue<int>(record) is var i && i == 0)
	|| (keyProperty.Type == typeof(long) && keyProperty.GetValue<long>(record) is var l && l == 0L)
	: false;
	sql.Append("INSERT");

AI/MEVD/src/SqliteVec/SqliteCommandBuilder.cs

Lines 228 to 235 in 215a5ba

	var parameterName = GetParameterName(property.StorageName, recordIndex);
	if (propertyIndex++ > 0)
	{
	sql.Append(", ");
	}
	sql.Append(parameterName);

AI/MEVD/src/SqliteVec/SqliteCommandBuilder.cs

Line 237 in 215a5ba

command.Parameters.Add(new SqliteParameter(parameterName, value ?? DBNull.Value));

AI/MEVD/src/SqliteVec/SqliteCommandBuilder.cs

Lines 557 to 558 in 215a5ba

	private static string GetParameterName(string propertyName, int index)
	=> $"@{propertyName}{index}";

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

[adamsitnik]

@rossdonald would you like to send a PR? We could include it in 1.0.2 release.