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

Question

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?

Answer 1

Deleted

Answer 2

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"

Answer 3

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

Answer 4

Durval, essa seria uma excelente solução, mas infelizmente é um requisito obrigatório no relatório.

Answer 5

Deleted

Answer 6

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"

Answer 7

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... 

Answer 8

Valeu pelas dicar Durval. Vou implementa-las.

Answer 9

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

 

Answer 10

Muito bem lembrado Durval

Answer 11

Deleted

Answer 12

Deleted

Answer 13

Isso. Muito Obrigada José.