Inquiridor
Desempenho de query

Pergunta
-
Olá pessoal!
Tudo certo?
Vim pedir uma ajuda, pois não consegui melhorar o desempenho com o que achei na net.
Essa query está consumindo muito o banco, podem me ajudar?
Obrigada!!
_________________________________________________________________________________________
SELECT TOP 5000
Z.DESCRICAO_LOCAL,
BAIXA.STATUS,
CASE
WHEN E.AREA=2 THEN '2. ATIBAIA'
WHEN E.AREA=3 THEN '3. GUARULHOS'
WHEN E.AREA=5 THEN '4. MOGI DAS CRUZES'
WHEN E.AREA=6 THEN '5. OSASCO'
WHEN E.AREA=8 THEN '6. SANTO AMARO'
WHEN E.AREA=9 THEN '7. SÃO MIGUEL PAULISTA'
WHEN E.AREA=4 THEN '8. TABOAO DA SERRA'
WHEN E.AREA in (' ',0,40,12,7) THEN '9. SEM UNIDADE CADASTRADA'
WHEN E.AREA in (1,11,10,14) THEN '1. SEDE' ELSE AREA.DESCRICAO END AS Unidade,
SUM(DEB.DEBITO) as 'Valor Débito' ,
(SELECT TOP (1) PAG.DTPAGTO FROM PAGAMENTOS as PAG WHERE PAG.EMPRESA = E.EMPRESA AND PAG.DTPAGTO >='2015-01-01' ORDER BY PAG.DTPAGTO DESC, PAG.RECNUM DESC) AS 'Data_Ultimo_Pagamento',
DEB.EMPRESA AS 'Código',
E.NOMEEMPRESA AS 'Razão Social',
E.CNPJ AS 'CNPJ',
ATIV.ATIVIDADE AS 'Atividade',
BAIXA.DESCRICAO AS 'Status',
E.QTDFUNC AS 'Qtd Empregados',
ATIV.DESCRICAO AS 'Atividade',
(CAST(E.LOGRAD AS VARCHAR(3)) + ' ' + CAST(E.COMPLEMENTO AS VARCHAR(4))+ ' ' + CAST(E.ENDER AS VARCHAR(40))+ ' ' + CAST(E.COMPL_ENDERECO AS VARCHAR(20))) AS "Endereço",
E.BAIRRO AS 'Bairro',
(CAST( E.DDD_1 AS VARCHAR(3)) + ' ' + CAST(E.FONE1P AS VARCHAR(5)) + '-' + CAST(E.FONE1S AS VARCHAR(5))) AS 'Telefone',
E.E_MAIL as 'E-mail 1',
(CAST(E.CEP AS VARCHAR(7)) + '-' + CAST(E.COMPL_CEP AS VARCHAR(5))) AS 'CEP'
FROM DEBITOSas DEB
LEFT JOIN EMPRESAS AS E ON DEB.EMPRESA = E.EMPRESA
LEFT JOIN BAIXAS AS BAIXA on E.CODIGOBAIXA= BAIXA.COD_BAIXA
LEFT JOIN AREAADM AS AREA on E.AREA= AREA.AREA_ADM
LEFT JOIN ATIVIDADES AS ATIV on E.CODIGOATIV =ATIV.COD_ATIV
LEFT JOIN ACORDOS AS ACOR ON E.EMPRESA = ACOR.EMPRESA
LEFT JOIN LOCALACORDO AS Z ON ACOR.ZONA_DRT= Z.ZONALOCAL
WHERE
E.CODIGOBAIXA=0 AND
E.DATATERMO='1753-01-01' AND
DEB.TPREC ='A' AND
E.CODIGOATIV NOT IN (32,8,1,11,2,59,60,7,27,28,57, 22,37,50,5,58,0,99) AND
DEB.DATAVENCE BETWEEN DATEADD(YEAR, -5 , GETDATE()) AND DATEADD(MONTH, -1 , GETDATE()) AND
DEB.DEBITO BETWEEN 200 AND 500 AND
DEB.DEBITO>0 AND
Z.ZONALOCAL=7
GROUP BY E.EMPRESA,DEB.EMPRESA,E.DATATERMO,E.TP_EMPR,E.NOMEEMPRESA,E.AREA,AREA.DESCRICAO,E.QTDFUNC , E.BAIRRO,E.LOGRAD ,E.COMPLEMENTO ,E.ENDER ,E.COMPL_ENDERECO,E.DDD_1 ,E.FONE1P ,E.FONE1S , E.E_MAIL,E.CEP,E.COMPL_CEP,BAIXA.STATUS,ATIV.ATIVIDADE,E.CODIGOATIV,
E.CNPJ,Z.DESCRICAO_LOCAL,BAIXA.DESCRICAO
ORDER BY [Valor Débito]
Todas as Respostas
-
Dá uma olhada no plano de execução (link abaixo). Esses campos de data no banco estão como datetime ou varchar?
-
Mariana,
Vamos por partes, poderia responder as questões abaixo:
1 - Esta query foi elaboradora por você?
2 - Qual é o objetivo da mesma?
3 - Qual deve ser o retorno?
4 - O que esta acontecendo durante a execução desta query? Você esta notando alguma lentidão ou travamento no SQL Server?
5 - Esta query é executada diretamente no SSMS ou através de alguma aplicação?
-------------------------------------------------------------------------
Sem muita análise, mas batendo o olho, já podemos conversar a sugerir algumas mudanças, talvez seja possível dividir esta query em pedaços utilizando o comando de CTE, isso vai depender da versão do SQL Server.
Bom, a princípio eu sugeria uma análise no que se refere:
1 - Ao uso da subquery para retornar a coluna 'Data_Ultimo_Pagamento';
2 - O comando CASE o qual apresenta diversas condições de análise;
3 - Sua claúsula Where com diversas condições que estão sendo implementadas e aplicadas como filtro;
4 - As condições aplicadas nas suas junções, bem como, as junções que estão utilizadas são necessárias?
5 - Se você estiver utilizando o SQL Server 2012 ou superior poderíamos substituir as conversões explícitas feitas com uso da função CAST() pela função Concat() a qual já realização a conversão diretamente.
6 - Note que você esta fazendo a soma de valores para gerar a coluna 'Valor Débito', a qual esta forçando a necessidade de agregarmos os valores. Pergunto realmente temos que fazer isso?
Pois bem, inicialmente dentro do possível verifique estes pontos indicados, nos retorne os resultados e vamos evoluíndo.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 28 de outubro de 2019 19:57
-
-
_____________________________________________________________
1 - Esta query foi elaboradora por você? Sim
2 - Qual é o objetivo da mesma?
E.CODIGOBAIXA=0 and --TRAZER AS EMPRESAS QUE ESTÃO COM CODIGO=0
E.DATATERMO='1753-01-01' AND -- QUE NÃO TEM TERMO (ESSA DATA SIGNIFICA QUEM NÃO TEM TERMO)
DEB.TPREC ='A' AND -- TODAS QUE TENHAM DEBITO DA CATEGORIA A
E.CODIGOATIV NOT IN (32,8,1,11,2,59,60,7,27,28,57, 22,37,50,5,58,0,99) AND --TODAS AS EMPRESAS QUE TEM OS CODIGOS DAS ATIVIDADES DIFERENTES DESTES
DEB.DATAVENCE BETWEEN DATEADD(YEAR, -5 , GETDATE()) AND DATEADD(MONTH, -1 , GETDATE()) AND -- PERÍODO DE DEBITO DOS ÚLTIMOS 5 ANOS
DEB.DEBITO BETWEEN 200 AND 500 AND -- COM VALOR DE R$200 A R$500
Z.ZONALOCAL=7 --E DO LOCAL 7
3 - Qual deve ser o retorno?
Empresas do cadastro com codigo 0, que não tem Termo, que não seja das atividades enumeradas, com débitos nos últimos 5 anos nos valores de R$200 a R$500 que o código do local seja 7.
4 - O que esta acontecendo durante a execução desta query? Você esta notando alguma lentidão ou travamento no SQL Server?
o resultado é lento e o DBA me falou que está causando lentidão5 - Esta query é executada diretamente no SSMS ou através de alguma aplicação?
Direto no SSMS
-
Mariana,
Certo, mas dizer que o resultado esta causando lentidão, um DBA é meio fora do comum, se ele é DBA o mesmo deve ser capaz de análise os planos de execução e demais elementos envolvidos.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Marina,
Um outro detalhe, por exemplo na linha do operador In(), passo os valores de uma forma sequencia crescente para ajudar o SQL Server a trazer os dados dentro deste intervalo, evite passar da forma que você esta informando:
(32,8,1,11,2,59,60,7,27,28,57, 22,37,50,5,58,0,99).
Além disso, as condições no Where, passei inicialmente as condições que envolvem valores numéricos e caracteres depois passe os valores de dados, ou seja, vamos implementar os filtros pensando em trazer o que é mais fixo e depois o que vai trabalhar com intervalos.
Outro ponto, realizer um o Order By através de um coluna calculada é algo muito mas muito custoso em relação a processamento dependendo o volume de dados, realmente trocar isso.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
- Editado Junior Galvão - MVPMVP quinta-feira, 10 de outubro de 2019 23:26
-
1 - Ao uso da subquery para retornar a coluna 'Data_Ultimo_Pagamento';
Não sei te responder, só consegui fazer dessa forma, onde faço a query em uma tabela de débitos, aí faço uma subquery para consultar a tabela de pagamentos e me retornar a última data de pagamento por empresa. Teria um jeito mais simples?
2 - O comando CASE o qual apresenta diversas condições de análise;
Tem como substituir este CASE por algo mais rápido?
3 - Sua claúsula Where com diversas condições que estão sendo implementadas e aplicadas como filtro;
Eu realmente preciso fazer essas checagens, acha que eu poderia colocar de outra forma?
4 - As condições aplicadas nas suas junções, bem como, as junções que estão utilizadas são necessárias?
Sim, é onde eu consigo trazer os nomes das coisas, nome da empresa, nome da atividade, nome da baixa...
5 - Se você estiver utilizando o SQL Server 2012 ou superior poderíamos substituir as conversões explícitas feitas com uso da função CAST() pela função Concat() a qual já realização a conversão diretamente.
o meu é o 2012 versão 11, mas não consegui usar o concat().
6 - Note que você esta fazendo a soma de valores para gerar a coluna 'Valor Débito', a qual esta forçando a necessidade de agregarmos os valores. Pergunto realmente temos que fazer isso?
Foi o jeito que encontrei de saber o valor total do débito por empresa, do contrário ele me traz várias linhas e não soma.
Obs: na verdade não foi um DBA, foi um tecnico de infra, acompanhando um consultor de DB. Não me disseram como arrumar isso.
-
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
Mariana,
5 - Veja este exemplo de uso da função CAST():
Concat(E.LOGRAD,' ',E.COMPLEMENTO,' ',E.ENDER,' ',E.COMPL_ENDERECO) AS "Endereço"
Um outro ponto de observação, este trecho: DEB.DATAVENCE BETWEEN DATEADD(YEAR, -5 , GETDATE()) AND DATEADD(MONTH, -1 , GETDATE()) AND
Fazer uso de funções que manipulando datas podem impactar na maneira que o SQL Server vai buscar os dados, não teria como você passar os valores ao invês de fazer estes cálculos.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
-
-
-
Deleted
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 28 de outubro de 2019 19:56
-
-
O concat não vai mesmo, mensagem retornada:
'Concat' is not a recognized built-in function name.
As instruções que vc me passou de reorganizar os critérios e order by já reduziram 5segundos da consulta
=)
Alterei para data fixa.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]