Melhor maneira de executar milhares de consultas de atualização em uma única tabela

Não há índice na coluna da cláusula where em myTable

Isso é um problema, pois sua mesa não é muito pequena.

Existe uma chave primária, porém eu pensei que se a coluna dentro da cláusula where tiver um índice, isso torna a atualização mais lenta?

Isso tornará a atualização real mais lenta, porque a página de índice precisa ser atualizada, assim como a página de dados base, mas o mecanismo de banco de dados primeiro precisa encontrar as linhas a serem atualizadas e isso é ajudado massivamente pela existência de um índice.

Sem índice na col2instrução update myTable set Col2 = Val4 where Col2 = Unq23, a tabela inteira, todas as 5.000.000 linhas, será verificada para localizar as linhas em que Col2 = Unq23. Mesmo que a tabela esteja inteiramente na memória, portanto, há pouca ou nenhuma E/S envolvida, isso consumirá tempo de CPU, portanto, demore um tempo mensurável que você precisará multiplicar pelas 60.000 execuções.

Não ter um índice irá poupar-lhe algumas escritas de página por update, mas vai custar-lhe dezenas ou centenas de milhares de leituras de página por update.

Existe uma maneira melhor de otimizar isso?

Atualizações em lote que fazem referência à mesma coluna no WHEREpredicado podem oferecer algum benefício de desempenho, mas espero que adicionar índices para dar suporte às atualizações individuais tenha um benefício muito maior, então sugiro resolver isso antes de tentar fazer algo mais complicado. Depois que os índices estiverem em vigor, se o desempenho ainda estiver muito lento, considere a refatoração do processo para atualizações em lote juntos.

Detalhes extras solicitados nos comentários:

Obrigado por esta informação! Eu adicionei o índice na cláusula where. Executá-lo 1000 blocos de linha parece estar bem, mas alguns blocos levam milissegundos e alguns levam até 10 minutos.

Você precisaria fazer alguns diagnósticos para verificar, mas meu primeiro pensamento se o banco de dados estiver atendendo ativamente a outras solicitações é que os lotes de execução mais longa estão sendo retidos por bloqueios mantidos por outras instruções de execução longa. Enquanto um dos lentos estiver rodando você pode verificar isso rodando EXEC sp_who2nesse banco de dados, você verá sua tarefa com algo na BlkBycoluna (essa informação é o SPID da conexão que está segurando). Para obter mais informações, você pode explorar as várias visualizações do sistema ou usar sp_whoisactive †, que faz muito trabalho braçal para você‡.

_{[†] existem alguns scripts/procedimentos utilitários semelhantes por aí, este é o que eu usei bastante}

_{[‡] Eu recomendo dar uma olhada nas visualizações de gerenciamento do sistema (quando o tempo permitir) para entender melhor o que eles estão fazendo, em vez de apenas tratar esses scripts como caixas pretas úteis, então você saberá o que fazer mais manualmente, se puder. t use os gostos de sp_whoisactive em algum lugar no futuro}

Se você é o único usuário desse banco de dados no momento, ou o problema não parece ser causado por um bloqueio assim, verifique as colunas CPUTime e DiskIO de sp_who*- esses acúmulos implicarão que algumas das atualizações ainda estão verificando em vez de procurar no índice, ou que algumas das atualizações estão modificando um número muito maior de linhas do que outras.

Seria melhor para mim inserir os valores que estou atualizando em uma tabela e depois fazer uma junção de atualização a partir daí?

Muito provavelmente, pois reduzirá a quantidade de páginas lidas e será capaz de unir as gravações em menos também, embora esteja ciente de que você está adicionando gravações extras ao banco de dados preenchendo essa tabela de retenção, portanto, tome cuidado para fazer isso com eficiência também. Certifique-se também de que a tabela tenha um índice apropriado para auxiliar a(s) instrução(ões) de atualização de junção e lote as inserções nessa tabela. Se este for um conjunto único (ou raro) de atualizações, talvez eu evite complicar o processo se a velocidade for lenta, mas aceitável sem.

Eu pensei que se a coluna dentro da cláusula where tivesse um índice, isso tornaria a atualização mais lenta?

Não, na verdade muito pelo contrário. O mecanismo de banco de dados precisa encontrar as linhas que você deseja atualizar com base em sua WHEREcláusula. Um índice organiza os dados, normalmente em uma estrutura de dados B-Tree, classificados nos campos especificados nele. B-Trees tem uma O(Log(n))complexidade de tempo de busca. A indexação de seus campos de predicado ( JOIN, WHEREe HAVINGcláusulas) organiza os dados para que sua WHEREcláusula possa ser executada com mais eficiência.

Sem um índice, a tabela inteira precisa ser verificada para localizar as linhas nas quais sua WHEREcláusula está filtrando. Digitalizar uma tabela de 5 milhões de linhas 60 mil vezes não será rápido. Na verdade, é uma O(n)operação de complexidade de tempo de pesquisa, o que significa que é exponencialmente mais lenta do que se sua tabela tivesse o índice adequado para pesquisar.

A desvantagem com os índices é que há operações de gravação adicionais que precisam ocorrer para mantê-los atualizados quando novas linhas são inseridas na tabela ou quando os campos indexados são alterados. Então, sim, com base em suas consultas, você está atualizando os mesmos campos que indexaria. Mas essa sobrecarga de gravação adicional provavelmente vale a pena, para acelerar o tempo de localização das linhas a serem atualizadas.

Além disso, devido à sobrecarga de gravação com índices, muitos índices são um problema tanto quanto muito pouco. Não tenho certeza se suas 60 mil declarações de atualização são todas filtradas em um conjunto exclusivo de campos. (Embora 60 mil campos em uma única tabela seja um design ruim de qualquer maneira, então espero que não.) Mas você definitivamente não gostaria de adicionar 60 mil índices a uma tabela. É importante elaborar cuidadosamente seus índices para incluir a combinação certa de campos para maximizar o número de consultas que eles cobrem.