En una de nuestras aplicaciones (C #) estamos insertando / actualizando un gráfico grande (cientos de inserciones y actualizaciones). Esto está envuelto en una transacción porque necesito todo para revertir en caso de error. Estamos usandoApuesto para ejecutar las declaraciones SQL.

Desafortunadamente, toda la operación está tomando entre 2 y 8 segundos. Eso es de 2 a 8 segundos, las tablas principales de nuestra base de datos están bloqueadas, lo que hace que otras aplicaciones se bloqueen o sufran bloqueos.

Determiné que una de las operaciones, inserta en una tabla que contiene más de 120 millones de registros, ocupa la mayor parte del tiempo, pero no estoy seguro de cómo podría optimizar esto.

Aproximadamente, ese gráfico de tabla se configura de la siguiente manera:

table A (
  id int primary_key,
  name nvarchar
)

table B (
    id int primary_key,
    a_id int foreign_key, # has an index
    name nvarchar
)

Al insertar datos enA También necesito insertar datos correspondientes enB. Por lo tanto, estoy usandoscope_identity() para obtener la identificación del registro y usarlo al insertar registros enB.

Pseudo-sabio esto se ve de la siguiente manera:

# open transaction
# perform other table inserts
#
# this is one of the slowest operations
for item in list_a
    id_a = exec "insert into A (name) values (" + item.name + "); select scope_identity() as int"

    for bar in item.list_b
        exec "insert into B (id_a, name) values (" + id_a + ", " + bar.name + ")"
#
# perform more operations
# commit transaction

Al buscar en Google esto, una de las soluciones es envolver esto en una transacción. Pero no sé cuál sería el rendimiento porque esto ya está envuelto en la transacción principal. ¿Resolvería el problema?

Se da una segunda solución insertando los registros usando ununion all select..., pero no sé si podría usar elscope_identity() en esa llamada

¿Cómo puedo mejorar este caso particular? ¿Qué otras cosas puedo hacer para acelerar la operación o evitar que otras aplicaciones sufran estos bloqueos?