Usuário com melhor resposta
Problema com desempenho em parâmetros de múltipla escolha.

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?
Respostas
-
Deleted
- Marcado como Resposta silvinha.pereira quinta-feira, 18 de junho de 2015 19:58
-
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_CUSTOSilvinha.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
Todas as Respostas
-
-
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" -
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 -
-
Deleted
- Marcado como Resposta silvinha.pereira quinta-feira, 18 de junho de 2015 19:58
-
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_CUSTOSilvinha.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
-
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 assim1011, 1012, 1013...
-
-
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
-
-
-
-