none
Desempenho de query RRS feed

  • 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]

                       

    quinta-feira, 10 de outubro de 2019 20:39

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?

    https://docs.microsoft.com/pt-br/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-2017

    quinta-feira, 10 de outubro de 2019 21:38
  • 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]

    quinta-feira, 10 de outubro de 2019 22:24
  • Obrigada, vou testar isso!

    =)

    quinta-feira, 10 de outubro de 2019 22:45
  • _____________________________________________________________

    1 - Esta query foi elaboradora por você? Sim

    2 - Qual é o objetivo da mesma? 

    E.CODIGOBAIXA=    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, -, GETDATE()) AND DATEADD(MONTH, -, 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ão 

    5 - Esta query é executada diretamente no SSMS ou através de alguma aplicação?

    Direto no SSMS

    quinta-feira, 10 de outubro de 2019 22:53
  • 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]

    quinta-feira, 10 de outubro de 2019 22:58
  • 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]

    quinta-feira, 10 de outubro de 2019 23:15
  • 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.

    quinta-feira, 10 de outubro de 2019 23:18

  • 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]

    sexta-feira, 11 de outubro de 2019 00:05
  • 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, -, GETDATE()) AND DATEADD(MONTH, -, 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]

    sexta-feira, 11 de outubro de 2019 00:05
  • 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.

    sexta-feira, 11 de outubro de 2019 01:19
  • Mariana, o que significa "consumindo muito o banco"?

    Como o Eliézer comentou, primeiro é necessário analisar o plano de execução para saber os possíveis gargalos. Caso não tenha experiência com planos de execução, sugiro que leia o artigo “O Plano Perfeito” e salve o plano de execução em arquivo no formato XML, disponibilizando-o então em algum repositório público.

    Na cláusula FROM foi utilizada junção aberta (LEFT OUTER) mas na cláusula WHERE há predicados com colunas das tabelas; isto transforma o LEFT OUTER em INNER.

    Como está declarada a coluna E.AREA? Achei estranho a comparação dela com valores numéricos e com texto:
         WHEN E.AREA in (' ',0,40,12,7)

    O predicado
        DEB.DEBITO>0
    na cláusula WHERE é redundante.

    Há colunas redundantes na cláusula GROUP BY.

    Sugiro também a leitura do artigo “Construindo códigos T-SQL eficientes”, que trata da construção de predicados sargable.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 11 de outubro de 2019 13:47
    sexta-feira, 11 de outubro de 2019 10:04
  • Mariana, em outro tópico você cita que a tabela DEBITOS possui (possuía) cerca de 600 mil linhas. Então, na parte de programação SQL uma tentativa é separar a parte de totalização da parte de apresentação dos resultados, pois assim serão necessários menos recursos de memória e de processamento na totalização.

    Por exemplo:

    -- código #1
    with
    -- totalização
    aggDEBITO as ( SELECT DEB.EMPRESA, ACOR.ZONA_DRT, sum (DEB.DEBITO) as somaDEBITO from DEBITOS as DEB inner join EMPRESAS as E on E.EMPRESA = DEB.EMPRESA inner join ACORDOS as ACOR on ACOR.EMPRESA = DEB.EMPRESA where -- filtros empresa E.CODIGOBAIXA = 0 and E.DATATERMO = '1753-01-01' and E.CODIGOATIV not in (32,8,1,11,2,59,60,7,27,28,57, 22,37,50,5,58,0,99) -- filtros débito and DEB.TPREC = 'A' and DEB.DATAVENCE between DATEADD(YEAR, -5 , GETDATE()) AND DATEADD(MONTH, -1 , GETDATE()) and DEB.DEBITO between 200 and 500 -- outros filtros and ACOR.ZONA_DRT = 7 group by DEB.EMPRESA, ACOR.ZONA_DRT ) -- apresentação SELECT Z.DESCRICAO_LOCAL as [Descrição local], B.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, DEB.somaDEBITO 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 último pagamento], E.EMPRESA as [Código], E.NOMEEMPRESA as [Razão Social], E.CNPJ, B.DESCRICAO as Status, E.QTDFUNC as [Qtd Empregados],        ATIV.ATIVIDADE,
    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 char(5)) + '-' + cast (E.COMPL_CEP as char(3)) as CEP from aggDEBITO as DEB inner join EMPRESAS as E on E.EMPRESA = DEB.EMPRESA left join BAIXAS as B on B.COD_BAIXA = E.CODIGOBAIXA left join AREAADM as AREA on AREA.AREA_ADM = E.AREA left join ATIVIDADES as ATIV on ATIV.COD_ATIV = E.CODIGOATIV left join LOCALACORDO as Z ON Z.ZONALOCAL = DEB.ZONA_DRT;

    Não testei; pode conter erro(s).

    Observe que na CTE aggDEBITO são utilizadas somente as colunas e tabelas necessárias para aplicação dos filtros. Isto reduz o consumo de memória.

    Preste atenção que no resultado há duas colunas com título STATUS e duas com o título ATIVIDADE; sugiro que as modifique.

    O resultado precisa ser apresentado em alguma ordem?

    De qualquer forma, somente analisando o plano de execução para saber as causas da lentidão e definir as otimizações.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    sexta-feira, 11 de outubro de 2019 11:08
  • Olá José Diniz!

    Muito obrigada pelas dicas! Vou testar e te retorno.

    :)

    sexta-feira, 11 de outubro de 2019 16:41
  • 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.

    Certo, isso indica que você esta utilizando uma versão anterior ao SQL Server 2012.

    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]

    sexta-feira, 11 de outubro de 2019 16:46