none
Problema com desempenho em parâmetros de múltipla escolha. RRS feed

  • Pergunta

  • Pessoal boa tarde.

    Eu tenho um parâmetro de filtro de centro de custos. @cc

    Esse parâmetro contém 144 opções.

    no SQL tenho o seguinte código:

    [CENTRO_CUSTO] in (@cc)

     Como já era de se esperar quando o usuário seleciona a opção "selecionar tudo" no report service, o desempenho do relatório cai absurdamente.

    Tentei encontrar uma forma de testar no sql se a opção "Selecionar tudo" está ativa, se sim, retiro o filtro, senão mantenho. Mas infelizmente não consegui. :-(

    Alguém tem alguma ideia de como resolver esse problema?

    quinta-feira, 18 de junho de 2015 14:01

Respostas

  • Deleted
    • Marcado como Resposta silvinha.pereira quinta-feira, 18 de junho de 2015 19:58
    quinta-feira, 18 de junho de 2015 15:28
  • José, consegui uma solução, mas não sei se é a melhor...

    Antes o relatório simplesmente não rodava. Agora ele roda em 24 seg. Ainda não é o ideal mas, já funciona.

    Segue o código que escrevi, se tiver alguma ideia para otimizar um pouco mais, agradeço muuito.

    --cria a tabela temporária

    IF OBJECT_ID('TempDB.dbo.#centroCusto') IS NOT NULL 


    drop table centroCusto


    else 

    --criação da tabela temporária
    Create table #centroCusto (
       centro_custo_temp varchar(MAX)
      
    )

    INSERT INTO  #centroCusto
    SELECT
    CC.cod_ccusto as centro_custo_temp
    FROM ccusto CC
    WHERE CC.cod_ccusto IN (@cc)



    -- busca as informações


    SELECT 
          [COD_CONTA]
          ,[OrdemExecucao]
          ,[cod_modul_dtsul]
          ,[descricao_modulo]
          ,dre.[CENTRO_CUSTO]
          ,[DATA_LANCAMENTO]
          ,[HISTORICO]
          ,[NUMERO_LOTE]
          ,[NUMERO_LANCAMENTO]
          ,[NUM_SEQ_CONTABIL]
          ,[COD_ESTAB]
          ,[PP]
          ,cast([Cr] as float(2)) as cr
          ,cast([Db]as float(2)) as db
          ,case when [COD_CONTA]not in(
    43100001,
    43100002,
    43100003,
    43100004,
    43100005,
    43100006) 
      then 0 
      else cast([Cr] as float(2)) end AS crd
      ,case when [COD_CONTA]not in(
    43100001,
    43100002,
    43100003,
    43100004,
    43100005,
    43100006) 
      then 0 
      else cast([Db]as float(2)) end AS dbd
      
          ,[NATUREZA_OPERACAO]
          ,[DESCR_CONTA]
          ,[DESCR_PP]
          ,pai.des_unid_negoc
          ,[COD_EMPRESA]
          ,[PLANO_CONTA]
          ,[ANO]
          ,[MES]
          ,[MODULO_DATASUL]
          ,[DET_UM]
          ,[GRUPO1]
          ,[GRUPO2]
          ,[GRUPO3]
          ,[GRUPO4]
          ,[GRUPO5]
          ,[nome_mes]
          ,[Segmento]
          ,[PP_TEC]
       FROM [Repositorio_Padtec].[dbo].[tabela_plan_base_dre] dre
     left join tabela_plan_pppai pai on dre.PP = pai.cod_unid_negoc_filho
     inner join #centroCusto cc_temp on cc_temp.centro_custo_temp = dre.CENTRO_CUSTO

    Silvinha.Pereira,

    Olhando seu script T-SQL eu sugiro duas ações:

    1 - Criar um índice não clusterizado na coluna "cod_ccusto" para obter os dados de modo mais eficiente. Caso esta coluna já esteja indexada (somente ela) como índice cluster, passe direto para a 2ªparte. Senão crie o índice com o script T-SQL abaixo:

    CREATE NONCLUSTERED INDEX IDX_CODCUSTO 
    ON ccusto ([cod_ccusto] ASC)
    WITH (FILLFACTOR = 75) ON [PRIMARY]
    GO

    2 - Altere a ordem da consulta das tabelas, porque em T-SQL "a ordem dos fatores altera o produto". Segue o script abaixo:

    FROM [Repositorio_Padtec].[dbo].[tabela_plan_base_dre] AS dre
    INNER JOIN #centroCusto AS cc_temp 
    ON dre.CENTRO_CUSTO = cc_temp.centro_custo_temp
    LEFT JOIN tabela_plan_pppai AS pai 
    ON pai.cod_unid_negoc_filho = dre.PP


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    • Marcado como Resposta silvinha.pereira quinta-feira, 18 de junho de 2015 19:58
    quinta-feira, 18 de junho de 2015 17:20

Todas as Respostas

  • Deleted
    quinta-feira, 18 de junho de 2015 14:07
  • Silvinha.Pereira,

    Acredito que a opção mais adequada é remover a opção "Allow multiple values" deste relatório.

    Para isso, selecione o seu parâmetro "CENTRO_CUSTO" e clique com botão direiro para selecionar a opção "Parameter Properties".

    Logo vai abrir uma janela "Paremeter Properties", selecione a aba "General" e então desmarque a opção "Allow multiple values" para que este relatório não faça a consulta por todos os seus centros de custos de uma só vez.

    Veja na imagem abaixo:

         

    Para maiores informações veja:

    https://technet.microsoft.com/pt-br/library/aa337396%28v=sql.105%29.aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    quinta-feira, 18 de junho de 2015 14:41
  • José, consegui uma solução, mas não sei se é a melhor...

    Antes o relatório simplesmente não rodava. Agora ele roda em 24 seg. Ainda não é o ideal mas, já funciona.

    Segue o código que escrevi, se tiver alguma ideia para otimizar um pouco mais, agradeço muuito.

    --cria a tabela temporária

    IF OBJECT_ID('TempDB.dbo.#centroCusto') IS NOT NULL 


    drop table centroCusto


    else 

    --criação da tabela temporária
    Create table #centroCusto (
       centro_custo_temp varchar(MAX)
      
    )

    INSERT INTO  #centroCusto
    SELECT
    CC.cod_ccusto as centro_custo_temp
    FROM ccusto CC
    WHERE CC.cod_ccusto IN (@cc)



    -- busca as informações


    SELECT 
          [COD_CONTA]
          ,[OrdemExecucao]
          ,[cod_modul_dtsul]
          ,[descricao_modulo]
          ,dre.[CENTRO_CUSTO]
          ,[DATA_LANCAMENTO]
          ,[HISTORICO]
          ,[NUMERO_LOTE]
          ,[NUMERO_LANCAMENTO]
          ,[NUM_SEQ_CONTABIL]
          ,[COD_ESTAB]
          ,[PP]
          ,cast([Cr] as float(2)) as cr
          ,cast([Db]as float(2)) as db
          ,case when [COD_CONTA]not in(
    43100001,
    43100002,
    43100003,
    43100004,
    43100005,
    43100006) 
      then 0 
      else cast([Cr] as float(2)) end AS crd
      ,case when [COD_CONTA]not in(
    43100001,
    43100002,
    43100003,
    43100004,
    43100005,
    43100006) 
      then 0 
      else cast([Db]as float(2)) end AS dbd
      
          ,[NATUREZA_OPERACAO]
          ,[DESCR_CONTA]
          ,[DESCR_PP]
          ,pai.des_unid_negoc
          ,[COD_EMPRESA]
          ,[PLANO_CONTA]
          ,[ANO]
          ,[MES]
          ,[MODULO_DATASUL]
          ,[DET_UM]
          ,[GRUPO1]
          ,[GRUPO2]
          ,[GRUPO3]
          ,[GRUPO4]
          ,[GRUPO5]
          ,[nome_mes]
          ,[Segmento]
          ,[PP_TEC]
       FROM [Repositorio_Padtec].[dbo].[tabela_plan_base_dre] dre
     left join tabela_plan_pppai pai on dre.PP = pai.cod_unid_negoc_filho
     inner join #centroCusto cc_temp on cc_temp.centro_custo_temp = dre.CENTRO_CUSTO

    quinta-feira, 18 de junho de 2015 15:09
  • Durval, essa seria uma excelente solução, mas infelizmente é um requisito obrigatório no relatório.

    quinta-feira, 18 de junho de 2015 15:10
  • Deleted
    • Marcado como Resposta silvinha.pereira quinta-feira, 18 de junho de 2015 19:58
    quinta-feira, 18 de junho de 2015 15:28
  • José, consegui uma solução, mas não sei se é a melhor...

    Antes o relatório simplesmente não rodava. Agora ele roda em 24 seg. Ainda não é o ideal mas, já funciona.

    Segue o código que escrevi, se tiver alguma ideia para otimizar um pouco mais, agradeço muuito.

    --cria a tabela temporária

    IF OBJECT_ID('TempDB.dbo.#centroCusto') IS NOT NULL 


    drop table centroCusto


    else 

    --criação da tabela temporária
    Create table #centroCusto (
       centro_custo_temp varchar(MAX)
      
    )

    INSERT INTO  #centroCusto
    SELECT
    CC.cod_ccusto as centro_custo_temp
    FROM ccusto CC
    WHERE CC.cod_ccusto IN (@cc)



    -- busca as informações


    SELECT 
          [COD_CONTA]
          ,[OrdemExecucao]
          ,[cod_modul_dtsul]
          ,[descricao_modulo]
          ,dre.[CENTRO_CUSTO]
          ,[DATA_LANCAMENTO]
          ,[HISTORICO]
          ,[NUMERO_LOTE]
          ,[NUMERO_LANCAMENTO]
          ,[NUM_SEQ_CONTABIL]
          ,[COD_ESTAB]
          ,[PP]
          ,cast([Cr] as float(2)) as cr
          ,cast([Db]as float(2)) as db
          ,case when [COD_CONTA]not in(
    43100001,
    43100002,
    43100003,
    43100004,
    43100005,
    43100006) 
      then 0 
      else cast([Cr] as float(2)) end AS crd
      ,case when [COD_CONTA]not in(
    43100001,
    43100002,
    43100003,
    43100004,
    43100005,
    43100006) 
      then 0 
      else cast([Db]as float(2)) end AS dbd
      
          ,[NATUREZA_OPERACAO]
          ,[DESCR_CONTA]
          ,[DESCR_PP]
          ,pai.des_unid_negoc
          ,[COD_EMPRESA]
          ,[PLANO_CONTA]
          ,[ANO]
          ,[MES]
          ,[MODULO_DATASUL]
          ,[DET_UM]
          ,[GRUPO1]
          ,[GRUPO2]
          ,[GRUPO3]
          ,[GRUPO4]
          ,[GRUPO5]
          ,[nome_mes]
          ,[Segmento]
          ,[PP_TEC]
       FROM [Repositorio_Padtec].[dbo].[tabela_plan_base_dre] dre
     left join tabela_plan_pppai pai on dre.PP = pai.cod_unid_negoc_filho
     inner join #centroCusto cc_temp on cc_temp.centro_custo_temp = dre.CENTRO_CUSTO

    Silvinha.Pereira,

    Olhando seu script T-SQL eu sugiro duas ações:

    1 - Criar um índice não clusterizado na coluna "cod_ccusto" para obter os dados de modo mais eficiente. Caso esta coluna já esteja indexada (somente ela) como índice cluster, passe direto para a 2ªparte. Senão crie o índice com o script T-SQL abaixo:

    CREATE NONCLUSTERED INDEX IDX_CODCUSTO 
    ON ccusto ([cod_ccusto] ASC)
    WITH (FILLFACTOR = 75) ON [PRIMARY]
    GO

    2 - Altere a ordem da consulta das tabelas, porque em T-SQL "a ordem dos fatores altera o produto". Segue o script abaixo:

    FROM [Repositorio_Padtec].[dbo].[tabela_plan_base_dre] AS dre
    INNER JOIN #centroCusto AS cc_temp 
    ON dre.CENTRO_CUSTO = cc_temp.centro_custo_temp
    LEFT JOIN tabela_plan_pppai AS pai 
    ON pai.cod_unid_negoc_filho = dre.PP


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    • Marcado como Resposta silvinha.pereira quinta-feira, 18 de junho de 2015 19:58
    quinta-feira, 18 de junho de 2015 17:20
  • Oi José.

    Então:
    Como está declarada a variável @cc?  Qual é a origem dela? Os centros de custos em uso são valores numéricos ou alfanuméricos? Poderia postar o exemplo de um deles?

    È um parâmetro de múltipla escolha que vem do  ms report builder. Ferramenta de relatórios.

    O parâmetro já vem como um array do tipo int, então seria algo mais ou meno assim

    1011, 1012, 1013... 

    quinta-feira, 18 de junho de 2015 19:40
  • Valeu pelas dicar Durval. Vou implementa-las.

    quinta-feira, 18 de junho de 2015 19:43
  • Durval... Impressionante! Somente com as alterações que vc mencionou reduziu o tempo em 12s.

    Entendi parte das alterações. Entendi que o Inner Join deve vir primeiro, mas pq foi da alteração abaixo:

    de:
     left join tabela_plan_pppai pai on dre.PP = pai.cod_unid_negoc_filho


    Para:

    LEFT JOIN tabela_plan_pppai AS pai 
    ON pai.cod_unid_negoc_filho = dre.PP

     

    quinta-feira, 18 de junho de 2015 19:57
  • Muito bem lembrado Durval
    quinta-feira, 18 de junho de 2015 20:00
  • Deleted
    quinta-feira, 18 de junho de 2015 20:37
  • Deleted
    quinta-feira, 18 de junho de 2015 20:44
  • Isso. Muito Obrigada José.
    sexta-feira, 19 de junho de 2015 13:05