O ajuste de desempenho de SQL pode ser uma tarefa incrivelmente difícil, especialmente 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á feita por um administrador de banco de dados (DBA). Mas acredite em mim, existem muitos desenvolvedores lá fora, que precisam realizar tarefas semelhantes às de um DBA. Além disso, em muitas das empresas, vi que Faz têm DBAs, eles geralmente lutam para trabalhar bem com os desenvolvedores - as posições simplesmente exigem diferentes modos de solução de problemas, o que pode levar a divergências entre os colegas de trabalho.
quantas pessoas devem estar em uma equipe scrum?Ao trabalhar com dados em grande escala, mesmo a menor alteração pode ter um impacto dramático no desempenho.
Além disso, a estrutura corporativa também pode desempenhar um papel. Digamos que a equipe de DBA esteja localizada 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 em uma estrutura de relatório completamente separada - certamente é difícil trabalhar juntos sem problemas nessas condições.
Neste artigo, gostaria de realizar duas coisas:
- Forneça aos desenvolvedores algumas técnicas de ajuste de desempenho de SQL do lado do desenvolvedor.
- Explique como os desenvolvedores e DBAs podem trabalhar juntos de forma eficaz.
Ajuste de desempenho de SQL (na base de código): índices
Se você é um novato completo em bancos de dados e até mesmo se perguntando “O que é SQL ajuste de desempenho ? ”, 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 aos registros ordenados. Isso significa que, depois de criar um índice, você pode selecionar ou classificar suas linhas mais rápido 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, 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 pesquisa 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 desempenho decrescente 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, entretanto, de que a eliminação de índices afetará todas as consultas em execução nessa tabela; portanto, essa abordagem só é recomendada ao trabalhar com uma única inserção grande.
Ajuste de SQL: planos de execução no SQL Server
A propósito: a ferramenta Execution Plan do SQL Server pode ser útil para a criação de índices.
Sua principal função é exibir graficamente os métodos de recuperação de dados escolhidos pelo otimizador de consulta 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 guia 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 “Faltando Detalhes do Índice…”. É simples assim!
( Clique para ampliar )
Ajuste de SQL: evite loops de codificação
Imagine um cenário no qual 1000 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 evite tais loops em seu código. Por exemplo, poderíamos transformar o snippet acima usando um único INSERT ou UPDATE declaração 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 seu WHERE cláusula evita atualizar o valor armazenado se ele corresponder ao valor existente. Essa otimizaçã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
PARA subconsulta correlacionada é aquele que usa valores da consulta pai. Este tipo de consulta SQL tende a ser executado fila a fila , uma vez para cada linha retornada pela consulta externa e, portanto, diminui o desempenho da consulta SQL. Novos desenvolvedores de SQL são frequentemente pegos estruturando suas consultas desta forma - porque geralmente é o caminho mais fácil.
ajuste de desempenho de banco de dados e otimização de consulta
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 revisar 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, examinamos Company tabela apenas uma vez, no início, e JUNTE-a com Customer mesa. 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 favoritas de otimização de SQL é 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 lugar. Considere uma tabela com centenas de colunas e milhões de linhas - se seu aplicativo realmente precisa de apenas algumas colunas, não há sentido em consultar todos os dados. É um grande desperdício de recursos. ( Para mais problemas, veja Aqui . )
Por exemplo:
como escrever um algoritmo de aprendizado de máquina
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 prevenir erros futuros do sistema e ajuste de desempenho SQL adicional. Por exemplo, se você estiver usando um INSERT... SELECT... e a tabela de origem mudou com a adição de uma nova coluna, você pode ter problemas, mesmo se essa coluna não for 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, entretanto, que existem algumas situações em que o uso de SELECT * poderia 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)
Tabelas temporárias geralmente aumenta a complexidade de uma consulta. Se seu código pode ser escrito de maneira simples e direta, sugiro evitar tabelas temporárias.
Mas se você tiver um procedimento armazenado com alguma manipulação de dados que não podes 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ê tem que unir uma grande tabela e há condições em tal tabela, você pode aumentar o desempenho do banco de dados transferindo seus dados em uma tabela temporária, e então fazendo uma junção em este . 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 ideia para situações em que você pode querer usar tabelas temporárias:
Imagine uma mesa de cliente com milhões de registros. Você tem que fazer uma junção em uma região específica. Você pode conseguir isso usando um SELECT INTO declaração 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 de SQL também evitam usar SELECT INTO para criar tabelas temporárias, dizendo que esse comando bloqueia o banco de dados tempdb, impedindo que outros usuários criem tabelas temporárias. Felizmente, este é corrigido em 7.0 e posterior .)
Como alternativa às tabelas temporárias, você pode considerar o uso de 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 de que precisamos em nossa subconsulta (ou seja, não usar 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 snippets de SQL retornarão os mesmos dados. Mas com tabelas temporárias, podemos, 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.
gráficos e tabelas de design gráfico
Finalmente, quando terminar de usar sua tabela temporária, exclua-a para limpar os recursos tempdb, em vez de apenas esperar que seja excluída automaticamente (como será quando sua conexão com o banco de dados for encerrada):
DROP TABLE #temp Ajuste de SQL: “Does My Record Exist?”
Esta técnica de otimização SQL refere-se ao uso de EXISTS(). Se você deseja verificar se existe um registro, use EXISTS() em vez de COUNT(). Enquanto COUNT() examina toda a tabela, contando todas as entradas que correspondem à sua condição, EXISTS() sairá assim que vir o resultado de que precisa. Isso vai te 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 de SQL com SQL Server 2016
Como os DBAs que trabalham com o SQL Server 2016 provavelmente sabem, a versão marcou uma mudança importante no padrões e gerenciamento de compatibilidade . Como uma versão principal, é claro, vem com novas otimizações de consulta, mas o controle sobre se elas são usadas agora é simplificado via sys.databases.compatibility_level.
Ajuste de desempenho do SQL (no escritório)
Administradores de banco de dados SQL (DBAs) e desenvolvedores geralmente entram em conflito por causa de questões relacionadas 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 juntos de forma eficaz.
Otimização de banco de dados para desenvolvedores:
-
Se o 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!
-
Mesmo se você for um modelador de dados SQL ninja, peça a um DBA para ajudá-lo com seu diagrama relacional. Eles têm muito a compartilhar e oferecer.
aprender a codificar em c ++
-
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 quaisquer alterações 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 como grandes perdas para a empresa. Ser paciente!
-
Não peça aos DBAs SQL para fazer alterações nos dados em um ambiente de produção. Se você deseja acessar o banco de dados de produção, deve ser responsável por todas as suas próprias alterações.
Otimização de banco de dados para DBAs do SQL Server:
-
Se você não gosta que perguntem sobre o banco de dados, dê a eles um painel de status em tempo real. Desenvolvedores sempre suspeitam do status de um banco de dados, e tal painel pode economizar tempo e energia de todos.
-
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. Isso economizará muito tempo para outras pessoas e também para você.
-
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 seja capaz de quebrar algumas regras em um momento crítico.
-
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 mudanças, planeje com antecedência e esteja pronto para a migração.
Compreender o básico
O que é o processamento de consulta em um DBMS?
Os sistemas de gerenciamento de banco de dados, como o SQL Server, precisam traduzir as consultas SQL que você fornece em instruções reais que eles precisam executar para ler ou alterar os dados no banco de dados. Após o processamento, o mecanismo de banco de dados também tenta otimizar automaticamente a consulta sempre que possível.
O que é otimização de consulta no SQL Server?
A otimização de consulta ocorre quando um desenvolvedor, ou o mecanismo de banco de dados, altera uma consulta de forma que o SQL Server seja capaz de retornar os mesmos resultados com mais eficiência. Às vezes é tão simples quanto usar EXISTS () ao invés de COUNT (), mas outras vezes a consulta precisa ser reescrita com uma abordagem diferente.
O que é ajuste de desempenho no SQL Server?
O ajuste de desempenho inclui otimização de consulta, otimização de código de cliente SQL, gerenciamento de índice de banco de dados e, em outro sentido, melhor coordenação entre desenvolvedores e DBAs.
Qual é a utilidade de um índice em SQL?
Um índice rastreia um subconjunto de dados de uma tabela de destino para que a seleção e a ordenação possam ser feitas com muito mais rapidez, sem que o servidor precise examinar até o último bit de dados dessa tabela.
Por que EXISTS () é mais rápido que COUNT ()?
EXISTS () para o processamento assim que encontra uma linha correspondente, enquanto COUNT () tem que contar todas as linhas, independentemente de você realmente precisar desse detalhe no final.
