Ajuste de desempenho do banco de dados SQL para desenvolvedores

O ajuste de desempenho do SQL pode ser uma tarefa incrivelmente difícil, principalmente ao trabalhar com dados em grande escala, onde até mesmo a menor alteração pode ter um impacto dramático (positivo ou negativo) no desempenho.

Em empresas de médio e grande porte, a maior parte do ajuste de desempenho do SQL será tratada por um Administrador de Banco de Dados (DBA). Mas acredite em mim, existem muitos desenvolvedores por aí que precisam executar tarefas semelhantes a DBA. Além disso, em muitas das empresas que tenho visto que têm DBAs, muitas vezes eles lutam para trabalhar bem com os desenvolvedores – as posições simplesmente exigem modos diferentes de solução de problemas, o que pode levar a divergências entre colegas de trabalho.

Além disso, a estrutura corporativa também pode desempenhar um papel. Digamos que a equipe de DBA esteja no 10º andar com todos os seus bancos de dados, enquanto os desenvolvedores estão no 15º andar, ou mesmo em um prédio diferente sob uma estrutura de relatórios completamente separada - certamente é difícil trabalhar juntos sem problemas nessas condições.

Neste artigo, gostaria de realizar duas coisas:

1. Forneça aos desenvolvedores algumas técnicas de ajuste de desempenho SQL do lado do desenvolvedor.
2. Explique como desenvolvedores e DBAs podem trabalhar juntos de forma eficaz.

Ajuste de desempenho do SQL (no Codebase): índices

Se você é um novato completo em bancos de dados e até se pergunta “O que é ajuste de desempenho SQL?”, você deve saber que a indexação é uma maneira eficaz de ajustar seu banco de dados SQL que geralmente é negligenciado durante o desenvolvimento. Em termos básicos, um índice é uma estrutura de dados que melhora a velocidade das operações de recuperação de dados em uma tabela de banco de dados, fornecendo pesquisas aleatórias rápidas e acesso eficiente de registros ordenados. Isso significa que, depois de criar um índice, você pode selecionar ou classificar suas linhas mais rapidamente do que antes.

Os índices também são usados ​​para definir uma chave primária ou índice exclusivo que garantirá que nenhuma outra coluna tenha os mesmos valores. É claro que a indexação de banco de dados é um tópico vasto e interessante ao qual não posso fazer justiça com esta breve descrição (mas aqui está um artigo mais detalhado).

Se você é novo em índices, recomendo usar este diagrama ao estruturar suas consultas:

Basicamente, o objetivo é indexar as principais colunas de busca e ordenação.

Observe que se suas tabelas são constantemente marteladas por INSERT , UPDATE e DELETE , você deve ter cuidado ao indexar - você pode acabar diminuindo o desempenho, pois todos os índices precisam ser modificados após essas operações.

Além disso, os DBAs geralmente descartam seus índices SQL antes de realizar inserções em lote de mais de um milhão de linhas para acelerar o processo de inserção. Depois que o lote é inserido, eles recriam os índices. Lembre-se, no entanto, que a eliminação de índices afetará todas as consultas executadas nessa tabela; portanto, essa abordagem só é recomendada ao trabalhar com uma única inserção grande.

Ajuste do SQL: planos de execução no SQL Server

A propósito: a ferramenta Execution Plan no SQL Server pode ser útil para criar índices.

Sua principal função é exibir graficamente os métodos de recuperação de dados escolhidos pelo otimizador de consultas do SQL Server. Se você nunca os viu antes, há um passo a passo detalhado.

Para recuperar o plano de execução (no SQL Server Management Studio), basta clicar em “Incluir Plano de Execução Real” (CTRL + M) antes de executar sua consulta.

Em seguida, uma terceira aba chamada “Plano de Execução” aparecerá. Você pode ver um índice ausente detectado. Para criá-lo, basta clicar com o botão direito do mouse no plano de execução e escolher a opção “Missing Index Details…”. É simples assim!

( Clique para ampliar )

Ajuste de SQL: evite loops de codificação

Imagine um cenário em que 1.000 consultas martelam seu banco de dados em sequência. Algo como:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Você deve evitar esses loops em seu código. Por exemplo, poderíamos transformar o snippet acima usando uma INSERT ou UPDATE exclusiva com várias linhas e valores:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Certifique-se de que sua cláusula WHERE evite atualizar o valor armazenado se corresponder ao valor existente. Uma otimização tão trivial pode aumentar drasticamente o desempenho da consulta SQL atualizando apenas centenas de linhas em vez de milhares. Por exemplo:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Ajuste de SQL: evite subconsultas SQL correlacionadas

Uma subconsulta correlacionada é aquela que usa valores da consulta pai. Esse tipo de consulta SQL tende a ser executada linha por linha, uma vez para cada linha retornada pela consulta externa e, portanto, diminui o desempenho da consulta SQL. Os novos desenvolvedores de SQL geralmente são pegos estruturando suas consultas dessa maneira, porque geralmente é o caminho mais fácil.

Aqui está um exemplo de uma subconsulta correlacionada:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

Em particular, o problema é que a consulta interna ( SELECT CompanyName… ) é executada para cada linha retornada pela consulta externa ( SELECT c.Name… ). Mas por que examinar a Company repetidamente para cada linha processada pela consulta externa?

Uma técnica de ajuste de desempenho SQL mais eficiente seria refatorar a subconsulta correlacionada como uma junção:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

Nesse caso, passamos pela tabela Company apenas uma vez, no início, e LIGAMOS a ela com a tabela Customer . A partir de então, podemos selecionar os valores que precisamos ( co.CompanyName ) de forma mais eficiente.

Ajuste de SQL: selecione com moderação

Uma das minhas dicas de otimização SQL favoritas é evitar SELECT * ! Em vez disso, você deve incluir individualmente as colunas específicas de que precisa. Novamente, isso parece simples, mas vejo esse erro em todo o lugar. Considere uma tabela com centenas de colunas e milhões de linhas — se seu aplicativo realmente precisa de apenas algumas colunas, não faz sentido consultar todos os dados. É um enorme desperdício de recursos. ( Para mais questões, veja aqui. )

Por exemplo:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Se você realmente precisa de cada coluna, liste explicitamente cada coluna. Isso não é tanto uma regra, mas sim um meio de evitar futuros erros do sistema e ajuste de desempenho SQL adicional. Por exemplo, se você estiver usando um INSERT... SELECT... e a tabela de origem foi alterada pela adição de uma nova coluna, você poderá ter problemas, mesmo que essa coluna não seja necessária para a tabela de destino, por exemplo:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Para evitar esse tipo de erro do SQL Server, você deve declarar cada coluna individualmente:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Observe, no entanto, que existem algumas situações em que o uso de SELECT * pode ser apropriado. Por exemplo, com tabelas temporárias – o que nos leva ao nosso próximo tópico.

Ajuste de SQL: o uso inteligente de tabelas temporárias (#Temp)

As tabelas temporárias geralmente aumentam a complexidade de uma consulta. Se seu código puder ser escrito de maneira simples e direta, sugiro evitar tabelas temporárias.

Mas se você tem um procedimento armazenado com alguma manipulação de dados que não pode ser tratada com uma única consulta, você pode usar tabelas temporárias como intermediárias para ajudá-lo a gerar um resultado final.

Quando você precisa unir uma tabela grande e há condições nessa tabela, você pode aumentar o desempenho do banco de dados transferindo seus dados em uma tabela temporária e, em seguida, fazendo uma junção nela . Sua tabela temporária terá menos linhas do que a tabela original (grande), então a junção terminará mais rápido!

A decisão nem sempre é simples, mas este exemplo lhe dará uma noção de situações em que você pode querer usar tabelas temporárias:

Imagine uma tabela de clientes com milhões de registros. Você tem que fazer uma junção em uma região específica. Você pode conseguir isso usando uma SELECT INTO e, em seguida, juntando-se à tabela temporária:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Nota: alguns desenvolvedores SQL também evitam usar SELECT INTO para criar tabelas temporárias, dizendo que esse comando bloqueia o banco de dados tempdb, impedindo outros usuários de criar tabelas temporárias. Felizmente, isso foi corrigido na versão 7.0 e posterior .)

Como alternativa às tabelas temporárias, você pode considerar usar uma subconsulta como uma tabela:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Mas espere! Há um problema com esta segunda consulta. Conforme descrito acima, devemos incluir apenas as colunas que precisamos em nossa subconsulta (ou seja, não usando SELECT * ). Levando isso em consideração:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Todos esses trechos de SQL retornarão os mesmos dados. Mas com tabelas temporárias, poderíamos, por exemplo, criar um índice na tabela temporária para melhorar o desempenho. Há uma boa discussão aqui sobre as diferenças entre tabelas temporárias e subconsultas.

Finalmente, quando você terminar com sua tabela temporária, exclua-a para limpar os recursos tempdb, em vez de apenas esperar que ela seja excluída automaticamente (como será quando sua conexão com o banco de dados for encerrada):

 DROP TABLE #temp

Ajuste de SQL: “Meu registro existe?”

Esta técnica de otimização SQL diz respeito ao uso de EXISTS() . Se você quiser verificar se existe um registro, use EXISTS() em vez de COUNT() . Enquanto COUNT() varre a tabela inteira, contando todas as entradas que correspondem à sua condição, EXISTS() sairá assim que encontrar o resultado necessário. Isso lhe dará melhor desempenho e código mais claro.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Ajuste de desempenho do SQL com o SQL Server 2016

Como os DBAs que trabalham com o SQL Server 2016 provavelmente sabem, a versão marcou uma mudança importante nos padrões e no gerenciamento de compatibilidade. Como uma versão principal, é claro que vem com novas otimizações de consulta, mas o controle sobre se elas são usadas agora é simplificado por meio sys.databases.compatibility_level .

Ajuste de desempenho do SQL (no escritório)

Administradores de banco de dados SQL (DBAs) e desenvolvedores muitas vezes entram em conflito com questões relacionadas a dados e não relacionadas a dados. Com base na minha experiência, aqui estão algumas dicas (para ambas as partes) sobre como se dar bem e trabalhar em conjunto de forma eficaz.

Tweet

Otimização de banco de dados para desenvolvedores:

1. Se seu aplicativo parar de funcionar repentinamente, pode não ser um problema de banco de dados. Por exemplo, talvez você tenha um problema de rede. Investigue um pouco antes de acusar um DBA!

2. Mesmo se você for um modelador de dados ninja SQL, peça a um DBA para ajudá-lo com seu diagrama relacional. Eles têm muito a compartilhar e oferecer.

3. DBAs não gostam de mudanças rápidas. Isso é natural: eles precisam analisar o banco de dados como um todo e examinar o impacto de qualquer mudança de todos os ângulos. Uma simples mudança em uma coluna pode levar uma semana para ser implementada – mas isso ocorre porque um erro pode se materializar em grandes perdas para a empresa. Ser paciente!

4. Não peça a DBAs SQL para fazer alterações de dados em um ambiente de produção. Se você deseja acessar o banco de dados de produção, você deve ser responsável por todas as suas próprias alterações.

Otimização de banco de dados para DBAs do SQL Server:

1. Se você não gosta de pessoas perguntando sobre o banco de dados, dê a elas um painel de status em tempo real. Os desenvolvedores sempre desconfiam do status de um banco de dados, e esse painel pode economizar tempo e energia de todos.

2. Ajude os desenvolvedores em um ambiente de teste/garantia de qualidade. Facilite a simulação de um servidor de produção com testes simples em dados do mundo real. Esta será uma economia de tempo significativa para os outros, bem como para você.

3. Os desenvolvedores passam o dia todo em sistemas com lógica de negócios alterada com frequência. Tente entender este mundo sendo mais flexível, e consiga quebrar algumas regras em um momento crítico.

4. Os bancos de dados SQL evoluem. Chegará o dia em que você terá que migrar seus dados para uma nova versão. Os desenvolvedores contam com novas funcionalidades significativas a cada nova versão. Em vez de se recusar a aceitar suas alterações, planeje com antecedência e esteja pronto para a migração.