Em um de nossos aplicativos (C #), estamos inserindo / atualizando um gráfico grande (centenas de inserções e atualizações). Isso está envolvido em uma transação porque eu preciso que a coisa toda seja revertida em caso de erro. Nós estamos usandoDapper para executar as instruções SQL.

Infelizmente, a operação inteira está demorando entre 2 a 8 segundos. Em 2 a 8 segundos, as tabelas principais em nosso banco de dados são bloqueadas, causando a paralisação de outros aplicativos ou a interrupção dos bloqueios.

Concluí que uma das operações, inserida em uma tabela que contém mais de 120 milhões de registros, está ocupando a maior parte do tempo, mas não tenho certeza de como otimizar isso.

Aproximadamente, esse gráfico de tabela é configurado da seguinte maneira:

table A (
  id int primary_key,
  name nvarchar
)

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

Ao inserir dados noA Eu também preciso inserir os dados correspondentes noB. Então, eu estou usandoscope_identity() para obter o ID do registro e usá-lo ao inserir registros noB.

Pseudo-sábio, é o seguinte:

# 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

Ao pesquisar no Google, uma das soluções é agrupar isso em uma transação. Mas não sei qual seria o desempenho, porque isso já está envolvido na transação pai. Isso resolveria o problema?

Uma segunda solução é fornecida inserindo os registros usando umunion all select..., mas não sei se eu poderia usar oscope_identity() nessa ligação.

Como posso melhorar esse caso em particular? Quais são algumas outras coisas que posso fazer para acelerar a operação ou impedir que outros aplicativos sofram desses bloqueios?