Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Cerrado
  • ¿Algo no está bien?
#54 1 comentario 0 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
68/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
csharp, sqlite
Área
database

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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
Lenguaje dominante
C#
Estrellas
8
Forks
8
Merge medio
1 d 15 h
PR fusionados (30 d)
8

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de CommunityToolkit/AI

Todos los issues de CommunityToolkit/AI

Issues similares

Más issues de C#

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.