[Resolvido] Problema com Performance

[Rafael Mitsunaka 11]

Pessoal,

 

Estou tendo um problema com a seguinte query, ela retorna mtos registros e por conta disso, fica uma tremenda carroça.

 

alguém pode me ajudar a dar uma otimizada nela?

já não sei mais o que fazer.

 

 

select convert(varchar,r.cod_ramo) + '-' +  r.Descricao as Ramo,
		convert(varchar(20),str_cod_suc) + '-' + su.str_nome_suc as sucursal,
		ab.Apolice,
		ai.Cod_Item,
		ab.Endosso,
		ab.Tipo_Endosso,
		convert(varchar(20),cob.cod_cobertura) + '-'+ cob.Descricao_Cobertura as cod_cobertura ,
		ab.Dt_Emissao,
		ab.Dt_Inicio_Vigencia,
		ab.Dt_Fim_Vigencia,
		ab.Cod_Segurado ,
		RIGHT(REPLICATE('0',3) + convert(varchar,ac.cod_produto),3)+ RIGHT(REPLICATE('0',3) + convert(varchar,ac.cod_sub_produto),3)+ '-'+convert(varchar(20), p.Descricao_Produto) as Descricao_Produto,

		case when  ab.cia_renova = '5495'  or  ab.cia_renova = '6207' then 'Renovação Zurich'
			when ab.cia_renova = NULL then 'Seguro Novo'
			when ab.cia_renova <> NULL and ab.cia_renova <> '5495'  and  ab.cia_renova <> '6207' then 'Renovação Externa'
		end cia_renova,		
		ai.cep,
		ai.UF,
		ai.municipio,
		ab.lmi_unico,			
		substring(ab.apolice_renova,0,3)as suc_anterior,
		ab.apolice_renova as apolice_ant,
		replace(convert(varchar,ac.imp_segurada),'.',',') as valor_is,
		replace(convert(varchar,ac.pr_cobertura),'.',',') as Premio_Cobertura,	
		replace(convert(varchar,ac.fr_cobertura),'.',',') as Franquia_Cobertura,
		replace(convert(varchar,aco.perc_comissao_normal),'.',',')perc_comissao_normal,
		ab.Cod_Operacao,			
		ab.cnpj_seg		,
		ab.cod_empresa,
		su.str_cod_suc,
		ab.cod_ramo, 
		ab.Num_Certificado,			
		case when re.tipo_movto = 'N' then re.Valor_Comissao
			 when re.tipo_movto = 'R' then re.Valor_Comissao * -1
			 when re.tipo_movto = 'C' then re.Valor_Comissao
		end  as valor_comissao_res,			
		re.tipo_movto,
		NULL as tip_franq,
		NULL as metro_quadrado,
		NULL as inspecao_risco,
		NULL as tip_imovel,
		NULL as constr_imovel,
		NULL as hazard_grade,
		NULL as sprinkler,
		NULL as alarme,
		NULL as perc_cedido_risco,
		case when re.tipo_movto = 'N' then re.valor_premio
			 when re.tipo_movto = 'R' then re.valor_premio * -1
			 when re.tipo_movto = 'C' then re.valor_premio
		end  as valor_premio_res,
		NULL as Cod_Canal_BW,
		NULL as cod_sic,
		NULL as cod_cl_ocup,
		NULL as cod_cl_enqdr,
		NULL as cod_categ,
		NULL as cod_exp,
		NULL as tipo_pessoa,			
		NULL as idade_imovel,
		NULL as item_ant,
		case when e.Cod_Empresa = '01' then 'SISE'
				when e.Cod_Empresa = '02' then 'TIA'
				when e.Cod_Empresa = '5495' then 'GAC'
				else e.descricao_empresa
		end	descricao_empresa,
		ab.perc_cedido_risco as perc_cedido_risco_coss,
		pl.Descricao_Plano		

       into #tmpEmissao	
from 
	apolice_base ab with (nolock)
	inner join apolice_item ai with (nolock) on  ab.cod_empresa = ai.cod_empresa
		and ab.cod_filial = ai.cod_filial
		and ab.cod_ramo = ai.cod_ramo
		and ab.apolice = ai.apolice
		and ab.endosso = ai.endosso		
		inner join apolice_item_cobertura ac with (nolock)  on ai.cod_empresa = ac.cod_empresa
		and ai.cod_filial = ac.cod_filial
		and ai.cod_ramo = ac.cod_ramo
		and ai.apolice = ac.apolice
		and ai.endosso = ac.endosso
		and ai.cod_item = ac.cod_item
	inner join apolice_comissao aco with (nolock) on ab.cod_empresa = aco.cod_empresa
		and ab.cod_filial = aco.cod_filial
		and ab.cod_ramo = aco.cod_ramo
		and ab.apolice = aco.apolice
		and ab.endosso = aco.endosso	
	inner join coberturas cob with (nolock)  on ac.Cod_Empresa = cob.Cod_Empresa
		and ac.cod_ramo = cob.cod_ramo
		and ac.Cod_Produto = cob.Cod_Produto
		and ac.Cod_Cobertura = cob.Cod_Cobertura
		and ac.Cod_Sub_Produto = cob.Cod_Sub_Produto	
	inner join ramos r with (nolock) on ab.cod_ramo = r.cod_ramo
									 and ab.cod_empresa = r.cod_empresa
	inner join empresa e with (nolock) on e.cod_empresa = ab.cod_empresa
	inner join sucursais su with (nolock) on ab.Cod_Filial = su.STR_cod_suc
	inner join produtos p with (nolock) on p.cod_empresa = ac.cod_empresa
		and p.cod_ramo = ac.cod_ramo
		and p.cod_produto = ac.cod_produto
		and p.cod_sub_produto = ac.cod_sub_produto
	left join brzdesenv101.resseguro.dbo.resseguro re  on ab.cod_empresa = re.cod_empresa
		and ab.cod_filial = re.cod_filial
		and ab.cod_ramo = re.cod_ramo
		and ab.apolice = re.apolice
		and ab.endosso = re.endosso
		and ab.Num_Certificado = re.Num_Certificado
	left join brzdesenv101.resseguro.dbo.plano pl on re.cod_empresa = pl.cod_empresa
		and re.cod_plano = pl.cod_plano	
where ab.Dt_Emissao Between'20110101' and '20111231'	  
	  and r.cod_ramo in (14,16,18,30,62,71)		  
	  and ab.cod_empresa = 1	  		  

group by 
	convert(varchar,r.cod_ramo) + '-' +  r.Descricao,
	su.str_cod_suc, 
	su.str_nome_suc, 
	ab.Apolice, 
	ai.Cod_Item, 
	ab.Endosso,
	ab.Tipo_Endosso,
	cob.Cod_Cobertura, 
	cob.Descricao_Cobertura, 
	ab.Dt_Emissao,
	ab.Dt_Inicio_Vigencia,  
	ab.Dt_Fim_Vigencia,
	ab.cod_segurado,
	p.Descricao_Produto,
	ab.cia_renova,
	ai.cep,
	ai.UF,
	ai.municipio, 
	ab.lmi_unico, 
	ab.apolice_renova,
	aco.perc_comissao_normal,
	ab.Cod_Operacao, 
	e.Cod_Empresa, 
	e.Descricao_Empresa,		
	ac.imp_segurada ,
	ac.pr_cobertura ,	
	ac.fr_cobertura ,
	ac.Cod_Ramo ,
	ac.Cod_Produto,
	ac.Cod_Sub_Produto,
	ab.cnpj_seg,
	ab.Num_Certificado,
	ab.cod_ramo,
	ab.cod_empresa
	,re.valor_premio,
	re.valor_Comissao
	,pl.Descricao_Plano,
	re.tipo_movto,
	ai.tip_franq,
	ai.metro_quadrado,
	ai.inspecao_risco,
	ai.tip_imovel,
	ai.constr_imovel,
	ai.hazard_grade,
	ai.sprinkler,
	ai.alarme,
	ab.perc_cedido_risco,		
	ab.Cod_Canal_BW,
	ai.cod_cl_ocup,
	ai.cod_cl_enqdr,
	ai.cod_categ,
	ab.cod_exp,
	ai.idade_imovel,
	ab.item_ant


order by ai.cod_item		

[Motta 645]

1º) Os joins são feitos pelos índices ?

2º) As estatisticas estão atualizadas ?

3º) Rodou o Plano de Execução da query ?

4º) ab.Dt_Emissao faz parte do índice ?

ab.Dt_Emissao tem data/hora ou só data ?

5º) Quantos registros estão envolvidos nesta query aproximadamente ?

------------------------------------------------------------------------

[Rafael Mitsunaka 11]

1º) Os joins são feitos pelos índices ?

Sim todos os indices estao nos joins

2º) As estatisticas estão atualizadas ?

Sim

3º) Rodou o Plano de Execução da query ?

Com as temporarias não consigo, me da um erro.

4º) ab.Dt_Emissao faz parte do índice ?

a data é apenas um filtro, meu relatório tem que ser anual, nesse caso 2011

ab.Dt_Emissao tem data/hora ou só data ?

campo somente data

5º) Quantos registros estão envolvidos nesta query aproximadamente ?

aproximadamente 2 milhões.

[Motta 645]

È um rel. agregado ?

Tem relatórios que demoram mesmo pois vão em bases grandes e buscam muitos dados (tipo 2011), não tem então muita otimização.

Aqui onde trabalho geramos algumas bases consolidadas que são extraídas durante noite/fim-de-semana e a geração do rel em si é rápida.

 

Outra coisa que vi é que parece estar consultado duas bases (ou owners) isto pode degradar.

[Rafael Mitsunaka 11]

Sim ,ele faz um link para servidores diferentes.

E ai que mora justamente o problema.

quando faz o Left buscando de outro servidor, a coisa desanda.

[Motta 645]

Não daria para copiar esta tabela para a base local, tipo uma agregação ?

Os dados são estásticos ?

 

 

Ou ver se a questão do tráfego entre servidores pode ser melhorada.

[Rafael Mitsunaka 11]

Sem chance, são de 2 sistemas diferentes.

 

eu consegui achar 1 falha minha no script que estava girando duplicidade em tudo.

consegui resolver.

 

agora eu tenho uma dúvida.

 

tenho o seguinte resultado:

Descricao_Plano	valor_premio_res	valor_comissao_res	Ramo	sucursal	Apolice	Cod_Item	Endosso	Tipo_Endosso
Excesso Danos	7.03	0.00	30-SEGURO BENFEITORIAS E PRODUTOS AGROPECUA	89-Goias                              	9186152	1	0	1
Excesso Danos	7.03	0.00	30-SEGURO BENFEITORIAS E PRODUTOS AGROPECUA	89-Goias                              	9186152	1	0	1
Plano QTA	76.60	32.76	30-SEGURO BENFEITORIAS E PRODUTOS AGROPECUA	89-Goias                              	9186152	1	0	1
Plano QTA	76.60	32.76	30-SEGURO BENFEITORIAS E PRODUTOS AGROPECUA	89-Goias                              	9186152	1	0	1

 

E eu queria agrupar mais ou menos assim:

 

Descricao_Plano Valor_premio

QTA - Excesso Danos 7,03 + 76,60

 

 

como eu faria?

[Motta 645]

Tente com CASE

 

WHEN Descricao_Plano IN ('Excesso Danos','Plano QTA')

[Rafael Mitsunaka 11]

a descrição é aleatória e vem do retorno do ultimo select.

 

nesse caso, vai variar sempre, de acordo com cada Plano.

 

eu pensei em case também, mas ficaria meio inviável.

[Motta 645]

Mas tem de ter um critério de agrupar, não ?

[Rafael Mitsunaka 11]

Consegui resolver, meu left tinha coisa errada.