slow - C#y SQL Server: la mejor manera de eliminar varias filas en "una sola vez" utilizando un procedimiento almacenado

sql server performance

Question

Sé que hay muchas de las mismas preguntas sujetas aquí en SO, mi pregunta es si quiero eliminar decir alrededor de 1K filas en lugar de pocas, dado un List<int> de RecordID , podría evitar el uso de una DataTable y enviar la lista traducida a una declaración:

string ParmRecordsToDelete_CsvWhereIN = "("
for(int CurIdx=0; CurIdx < RecIdsToDelete.Count; CurIdx++)
{
  ParmRecordsToDelete_CsvWhereIN += RecIdsToDelete[CurIdx] + ", ";
  //this method to create passed parameter 
  //logic to remove on last Coma on last Index..

  //or use stringJoin and somehow remove the last coma 
}
ParRecordsToDelete_CsvWhereIN +=")";

Esto creará algo como "(''1'', ''2'', ''3'' ......)"

y luego crea un SqlCommand para llamar a un procedimiento almacenado:

Delete * From @TblName WHERE @ColName IN @RecordsToDeleteCsvWhereIN

¿Es este un enfoque eficiente? ¿Hay un límite en la longitud de un solo parámetro? Supongo que es la longitud N/VARCHAR(MAX) .

Supongo que si no es una solución poco hackish, no se limitaría con la longitud ...

¿Cuál sería la mejor solución rápida, o estoy en el camino correcto?

Answer 1

Puede usar los parámetros de tabla valorados para entregar esto. La capa de aplicación se vería como

DO#

var tvp = new DataTable();
tvp.Columns.Add("Id", typeof(int));

foreach(var id in RecIdsToDelete)
    tvp.Rows.Add(new {id});

var connection = new SqlConnection("your connection string");

var delete = new SqlCommand("your stored procedure name", connection)
{
  CommandType = CommandType.StoredProcedure
};

delete
  .Parameters
  .AddWithValue("@ids", tvp)
  .SqlDbType = SqlDbType.Structured;

delete.ExecuteNonQuery();

SQL

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = ''IDList'')
BEGIN
    CREATE TYPE IDList AS TABLE(ID INTEGER)
END


CREATE PROCEDURE School.GroupStudentDelete
(                                         
        @IDS IDLIST READONLY      
)                                         
AS

SET NOCOUNT ON;

BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @Results TABLE(id INTEGER)

        DELETE 
        FROM TblName 
        WHERE Id IN (SELECT ID FROM @IDS)        

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        PRINT ERROR_MESSAGE();

        ROLLBACK TRANSACTION
        THROW; -- Rethrow exception
END CATCH
GO

Este enfoque tiene varias ventajas sobre la construcción de cadenas

• Evita crear consultas de creación en la capa de aplicación creando una separación de preocupaciones
• Puede probar más fácilmente planes de ejecución y optimizar consultas
• Usted es menos vulnerable a los ataques de inyección SQL, ya que su enfoque dado no podría usar una consulta paramaterizada para construir la cláusula IN
• El código es más legible e ilustrativo
• No terminas construyendo cadenas excesivamente largas

Actuación

Hay algunas consideraciones sobre el rendimiento de los TVP en grandes conjuntos de datos.

Debido a que los TVP son variables, no compilan estadísticas. Esto significa que el optimizador de consultas puede modificar el plan de ejecución algunas veces. Si esto sucede, hay un par de opciones:

• establecer OPTION (RECOMPILE) en cualquier declaración TVP donde la indexación sea un problema
• escribe el TVP en una temperatura local y configura la indexación allí

Aquí hay un excelente artículo sobre TVP con una buena sección sobre consideraciones de rendimiento, y qué esperar cuando.

Por lo tanto, si está preocupado por alcanzar los límites de los parámetros de cadena, los parámetros de la tabla podrían ser el camino a seguir. Pero al final, es difícil de decir sin saber más sobre el conjunto de datos con el que está trabajando.

Answer 2

Un mejor enfoque sería usar un parámetro con valores de tabla. Debe definir un tipo para el parámetro, pero es más eficiente que especificar valores en una cadena, especialmente valores numéricos o de fecha porque el servidor no tiene que analizar la cadena para obtener los valores individuales.

No estoy seguro de cuál es su tipo de identificación, pero si es ''BIGINT'', por ejemplo:

IF NOT EXISTS (SELECT * FROM dbo.systypes WHERE name=''IDList'')
    CREATE TYPE IDList AS TABLE (Id BIGINT);
GO

Para inicializar el tipo, luego para crear un procedimiento almacenado usándolo, algo como esto:

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name=''DeleteMultipleRecords'')
   EXECUTE sp_executesql N''CREATE PROCEDURE DeleteMultipleRecords AS BEGIN SET NOCOUNT ON; END''
GO
ALTER PROCEDURE [dbo].[DeleteMultipleRecords]
   @IDs IDList READONLY
AS
BEGIN
   SET NOCOUNT ON

    DELETE FROM [Table] WHERE Id IN (SELECT Id FROM @IDs)
END

También puede usarlo con SQL dinámico desde C #.

Answer 3

IN @Parameter no es una opción, tal cosa no funciona. Puede conectar los identificadores a IN (1,2,3,4...) , pero eso es malo. Medí algunas selecciones livianas de 30000 ids:

TVP: 339ms first run, 319ms second run
hard wired: 67728ms first run, 42ms second run

Como puede ver, cuando SQL Server tiene que analizar cadenas enormes, lleva mucho tiempo. En la segunda ejecución, el plan de consulta puede tomarse de la caché del plan de ejecución, desafortunadamente con grandes rangos de id. Esto es extremadamente improbable. Simplemente desperdicia el caché del plan de ejecución.

TVP puede escalar en millones de identificadores sin problemas, la cadena cableada hace que el servidor sql falle la consulta de menos de 100000 identificadores. No tiene nada que ver con la longitud máxima de la cadena, simplemente no puede procesarla.

Por cierto. Si construyes una cadena como esta, usa StringBuilder o string.Join, agregar a string in loop es muy ineficiente.