SQL Server Acelerar procedure
01/08/2020
0
Preciso melhorar a performance dessa procedure. Vou detalhar como me recordo dela.
Create proc... @id,@empresa
Index #tempg...
Select v.a
V.b
V.c
V.d
V.e
#Tempg
From tableG v
Index #temph...
Select h.a
h.b
h.c
h.d
h.e
Into
#Tempg
From tableH h
Select 10 colunas, Max(op.previsaoconslusao) as Previsaoconclusao
into #tempop from pedidoscandidatos PC
Inner Join pedidos p on p.numero = PC.numero and p.empresa= PC.empresa
Tabela que tem alias pa
Mais uns 3 inners
Aí adicionei essa tabela que foi a partir daí que começou a travar
Left Join opitens op on op.item = Pa.item
Op.empresa = pa.empresa
Group by
Todas colunas menos
Previsaoconclusao
Select 12 colunas
From pedidos candidatos PC
3 ou 4 inners
As 3 temp
Embaixo possui ifs para update com where.
Desculpe se o código não ajudar, mas é o que consigo hoje
Se tiverem algumas dicas de como melhorar o código ou melhorar a performance de uma procedure agradeço!
Create proc... @id,@empresa
Index #tempg...
Select v.a
V.b
V.c
V.d
V.e
#Tempg
From tableG v
Index #temph...
Select h.a
h.b
h.c
h.d
h.e
Into
#Tempg
From tableH h
Select 10 colunas, Max(op.previsaoconslusao) as Previsaoconclusao
into #tempop from pedidoscandidatos PC
Inner Join pedidos p on p.numero = PC.numero and p.empresa= PC.empresa
Tabela que tem alias pa
Mais uns 3 inners
Aí adicionei essa tabela que foi a partir daí que começou a travar
Left Join opitens op on op.item = Pa.item
Op.empresa = pa.empresa
Group by
Todas colunas menos
Previsaoconclusao
Select 12 colunas
From pedidos candidatos PC
3 ou 4 inners
As 3 temp
Embaixo possui ifs para update com where.
Desculpe se o código não ajudar, mas é o que consigo hoje
Se tiverem algumas dicas de como melhorar o código ou melhorar a performance de uma procedure agradeço!
Bruno
Curtir tópico
+ 0
Responder
Posts
02/08/2020
Bruno
Preciso melhorar a performance dessa procedure. Vou detalhar como me recordo dela.
Create proc... @id,@empresa
Index #tempg...
Select v.a
V.b
V.c
V.d
V.e
#Tempg
From tableG v
Index #temph...
Select h.a
h.b
h.c
h.d
h.e
Into
#Tempg
From tableH h
Select 10 colunas, Max(op.previsaoconslusao) as Previsaoconclusao
into #tempop from pedidoscandidatos PC
Inner Join pedidos p on p.numero = PC.numero and p.empresa= PC.empresa
Tabela que tem alias pa
Mais uns 3 inners
Aí adicionei essa tabela que foi a partir daí que começou a travar
Left Join opitens op on op.item = Pa.item
Op.empresa = pa.empresa
Group by
Todas colunas menos
Previsaoconclusao
Select 12 colunas
From pedidos candidatos PC
3 ou 4 inners
As 3 temp
Embaixo possui ifs para update com where.
Desculpe se o código não ajudar, mas é o que consigo hoje
Se tiverem algumas dicas de como melhorar o código ou melhorar a performance de uma procedure agradeço!
Create proc... @id,@empresa
Index #tempg...
Select v.a
V.b
V.c
V.d
V.e
#Tempg
From tableG v
Index #temph...
Select h.a
h.b
h.c
h.d
h.e
Into
#Tempg
From tableH h
Select 10 colunas, Max(op.previsaoconslusao) as Previsaoconclusao
into #tempop from pedidoscandidatos PC
Inner Join pedidos p on p.numero = PC.numero and p.empresa= PC.empresa
Tabela que tem alias pa
Mais uns 3 inners
Aí adicionei essa tabela que foi a partir daí que começou a travar
Left Join opitens op on op.item = Pa.item
Op.empresa = pa.empresa
Group by
Todas colunas menos
Previsaoconclusao
Select 12 colunas
From pedidos candidatos PC
3 ou 4 inners
As 3 temp
Embaixo possui ifs para update com where.
Desculpe se o código não ajudar, mas é o que consigo hoje
Se tiverem algumas dicas de como melhorar o código ou melhorar a performance de uma procedure agradeço!
Alguma dica por favor?
Responder
03/08/2020
Bruno
ALTER PROCEDURE pProgramacaoPedidosEmLote_PopulaPedidosCandidato @Empresa int
,@Marca int
as
begin
begin try
set nocount on
if exists (
select top 1 1
from dbo.Parametros
where AnalisaPedidosCandidatosSemaforo = 1
)
raiserror (
'Job em processamento, por favor aguarde alguns instantes!'
,16
,1
)
if OBJECT_ID('tempdb..#ProgramacaoPedidosEmLote_PedidosCandidato') is null
begin
declare @ProgramacaoPedidosEmLote_PedidosCandidato TY_ProgramacaoPedidosEmLote_PedidosCandidato;
select *
into #ProgramacaoPedidosEmLote_PedidosCandidato
from @ProgramacaoPedidosEmLote_PedidosCandidato
end
else
delete
from #ProgramacaoPedidosEmLote_PedidosCandidato
set nocount off
--declare @emp int = 8
-- ,@marca int = 5
declare @Lemp int
,@Lmarca int;
select @Lemp = @Empresa
,@LMarca = @marca;
if not (object_id('tempdb..#tempemp') is null)
drop table #tempemp;
select *
into #tempemp
from dbo.fParEmpSelect(@Lemp);
if not (object_id('tempdb..#tempCandidatos') is null)
drop table #tempCandidatos;
select Empresa,Item,PrevisaoConclusao into #tempopitens
from OPItens;
with TempPedidosEmpenhados
as (
select distinct empresa
,numero
from dbo.pedidosempenhados
group by empresa
,numero
)
select tp.Volumes
,tp.VolumesAcessorios as VolAcessorios
,case
when pf.idPedido is null
then 'Bloqueado'
else 'Liberado'
end LibFinan
,tp.VolumesZero
,tp.TVolumes
,tp.TVolumesColetados
,tp.TVolumesAtendidos
,p.Data_Liberacao as Data
,p.Ordem_Compra
,c.Razao_Social
,e.Estado as UF
,substring(e.cidade, 1, 15) as Cidade
,t1.Fantasia as Transp
,case p.Tipo_Frete
when 1
then 'FOB'
when 2
then 'CIF'
when 3
then 'CORT'
when 4
then 'CIFSP'
else 'INDEF'
end as Frete
,p.Observacao_1 as OBS1
,p.Observacao_2 as OBS2
,p.Observacao_3 as OBS3
,convert(bit, case
when (vd.idPedido is not null)
then 1
else 0
end) as PossuiVidro
,p.Marca
,p.Transportadora
,pp.Descricao as TipoPedido
,t.Empresa
,t.Numero
,convert(bit, case
when ca.Codigo_Cliente is null
then 0
else 1
end) as InfAgenda
,p.Data_Base as PedidoData_Base
,c.Codigo_Cliente as Cliente
,p.bObs1
,p.bObs2
,p.bObs3
,convert(bit, iif((ObsRomaneio & Power(2, 1 - 1)) > 0, 1, 0)) as bObsRomaneio1
,convert(bit, iif((ObsRomaneio & Power(2, 2 - 1)) > 0, 1, 0)) as bObsRomaneio2
,convert(bit, iif((ObsRomaneio & Power(2, 3 - 1)) > 0, 1, 0)) as bObsRomaneio3
,max(op.PrevisaoConclusao) as PrevisaoConclusao
into #tempCandidatos
from dbo.PedidosCandidatos t
inner join dbo.pedidos p on p.empresa = t.empresa
and p.numero = t.numero
left join dbo.Clientes_Agendamento ca on ca.codigo_cliente = p.cliente
inner join dbo.TiposPedido pp on pp.Codigo = p.Tipo_Pedido --14/09/15 leonice
inner join dbo.pedidosTotais tp on tp.empresa = t.empresa
and tp.numero = t.numero
inner join dbo.Transp t1 on t1.codigo_transp = p.transportadora
inner join dbo.clientes c on c.codigo_cliente = p.cliente
inner join dbo.Endereco e on e.cliente = p.cliente
and e.sequencia = p.endereco_entrega
inner join itensped ip on ip.Numero = t.numero
and ip.Empresa = t.empresa
left join #tempopitens op on op.empresa = ip.empresa
and op.Item = ip.Item
left join dbo.PedidosLiberaFaturamento pf on pf.idPedido = p.id
left join dbo.vPedidosVidrosMyHome vd on vd.idPedido = p.id
left join TempPedidosEmpenhados pd on pd.empresa = t.empresa
and pd.numero = t.numero
-- adicionar Kasa K à regra para trazer pedidos não programados aqui...
where (
(pd.empresa is null)
or (
(p.marca in (6, 9))
and (p.Data_Programacao is null)
)
)
group by tp.Volumes
,tp.VolumesAcessorios
,pf.idPedido
,tp.VolumesZero
,tp.TVolumes
,tp.TVolumesColetados
,tp.TVolumesAtendidos
,p.Data_Liberacao
,p.Ordem_Compra
,c.Razao_Social
,e.Estado
,e.cidade
,t1.Fantasia
,p.Tipo_Frete
,p.Observacao_1
,p.Observacao_2
,p.Observacao_3
,vd.idPedido
,p.Marca
,p.Transportadora
,pp.Descricao
,t.Empresa
,t.Numero
,ca.Codigo_Cliente
,p.Data_Base
,c.Codigo_Cliente
,p.bObs1
,p.bObs2
,p.bObs3
,ObsRomaneio;
select [id]
,Descricao
into #tempMarcas
from dbo.marcas
where [id] = @Lmarca
union
select m.[id]
,m.Descricao
from dbo.marcas m
inner join dbo.usuarios_marcas um on um.idMarca = m.id
and um.usuario = dbo.fUsuarioKappes()
where @Lmarca = 0
insert into #ProgramacaoPedidosEmLote_PedidosCandidato (
MarcaDescricao
,empresa
,numero
,Prioridade
,Aprovado
,Faturamento
,inclusao
,VidrosMyHome
,DataPrevistaColeta
,Volumes
,VolAcessorios
,LibFinan
,VolumesZero
,TVolumes
,TVolumesColetados
,TVolumesAtendidos
,Data
,Ordem_Compra
,Data_Base
,Razao_Social
,UF
,Cidade
,Transp
,Frete
,OBS1
,OBS2
,OBS3
,PossuiVidro
,TipoPedido
,Marca
,Agendado
,Agendamento
,Estruturas
,Lotes
,PedidoData_Base
,Cliente
,AdicionaEmRomaneio
,Codigo_Transp
,bObs1
,bObs2
,bObs3
,bObsRomaneio1
,bObsRomaneio2
,bObsRomaneio3
,PrevisaoConclusao
)
select distinct m.Descricao as Marca
,pc.*
,t.Volumes
,t.VolAcessorios
,t.LibFinan
,t.VolumesZero
,t.TVolumes
,t.TVolumesColetados
,t.TVolumesAtendidos
,t.Data
,t.Ordem_Compra
,pc.Faturamento as Data_Base
,t.Razao_Social
,t.UF
,t.Cidade
,t.Transp
,t.Frete
,t.OBS1
,t.OBS2
,t.OBS3
,t.PossuiVidro
,t.TipoPedido
,t.Marca
,pa.Agendado
,t.InfAgenda as Agendamento
,pr.Atendido as Estruturas
,'' as Lotes
,PedidoData_Base
,t.Cliente
,1 as AdicionaEmRomaneio
,t.Transportadora
,t.bObs1
,t.bObs2
,t.bObs3
,t.bObsRomaneio1
,t.bObsRomaneio2
,t.bObsRomaneio3
,t.PrevisaoConclusao
from dbo.PedidosCandidatos pc
left join dbo.PrgMetalAtendidos pr on pr.empresa = pc.empresa
and pr.numero = pc.numero
inner join dbo.itensped ip on ip.empresa = pc.empresa
and ip.numero = pc.numero
--inner join dbo.vitenscompostos ic on ic.referencia = ip.item
inner join dbo.vMovel m1 on m1.referencia = ip.item
inner join #tempCandidatos t on t.empresa = pc.empresa
and t.numero = pc.numero
inner join #tempMarcas m on m.[id] = m1.marca
inner join #tempemp pe on pe.empresa = t.empresa
left join [dbo].[Pedidos_Agendamento] pa on pa.empresa = pc.empresa
and pa.numero = pc.numero
-- where (pc.numero = 2258069) or not (suser_sname() = 'KAPPESBERG\\juliano')
order by pc.Prioridade desc
,pc.Faturamento;
update #ProgramacaoPedidosEmLote_PedidosCandidato
set AdicionaEmRomaneio = 0
,MotivoNaoAddRomaneio = 'Não deve gerar Romaneio para pedidos Idelli, My Home e Premium'
where Marca in (2, 6, 11) --Pedidos Idelli, My Home e Premium não deve gerar Romaneio
,@Marca int
as
begin
begin try
set nocount on
if exists (
select top 1 1
from dbo.Parametros
where AnalisaPedidosCandidatosSemaforo = 1
)
raiserror (
'Job em processamento, por favor aguarde alguns instantes!'
,16
,1
)
if OBJECT_ID('tempdb..#ProgramacaoPedidosEmLote_PedidosCandidato') is null
begin
declare @ProgramacaoPedidosEmLote_PedidosCandidato TY_ProgramacaoPedidosEmLote_PedidosCandidato;
select *
into #ProgramacaoPedidosEmLote_PedidosCandidato
from @ProgramacaoPedidosEmLote_PedidosCandidato
end
else
delete
from #ProgramacaoPedidosEmLote_PedidosCandidato
set nocount off
--declare @emp int = 8
-- ,@marca int = 5
declare @Lemp int
,@Lmarca int;
select @Lemp = @Empresa
,@LMarca = @marca;
if not (object_id('tempdb..#tempemp') is null)
drop table #tempemp;
select *
into #tempemp
from dbo.fParEmpSelect(@Lemp);
if not (object_id('tempdb..#tempCandidatos') is null)
drop table #tempCandidatos;
select Empresa,Item,PrevisaoConclusao into #tempopitens
from OPItens;
with TempPedidosEmpenhados
as (
select distinct empresa
,numero
from dbo.pedidosempenhados
group by empresa
,numero
)
select tp.Volumes
,tp.VolumesAcessorios as VolAcessorios
,case
when pf.idPedido is null
then 'Bloqueado'
else 'Liberado'
end LibFinan
,tp.VolumesZero
,tp.TVolumes
,tp.TVolumesColetados
,tp.TVolumesAtendidos
,p.Data_Liberacao as Data
,p.Ordem_Compra
,c.Razao_Social
,e.Estado as UF
,substring(e.cidade, 1, 15) as Cidade
,t1.Fantasia as Transp
,case p.Tipo_Frete
when 1
then 'FOB'
when 2
then 'CIF'
when 3
then 'CORT'
when 4
then 'CIFSP'
else 'INDEF'
end as Frete
,p.Observacao_1 as OBS1
,p.Observacao_2 as OBS2
,p.Observacao_3 as OBS3
,convert(bit, case
when (vd.idPedido is not null)
then 1
else 0
end) as PossuiVidro
,p.Marca
,p.Transportadora
,pp.Descricao as TipoPedido
,t.Empresa
,t.Numero
,convert(bit, case
when ca.Codigo_Cliente is null
then 0
else 1
end) as InfAgenda
,p.Data_Base as PedidoData_Base
,c.Codigo_Cliente as Cliente
,p.bObs1
,p.bObs2
,p.bObs3
,convert(bit, iif((ObsRomaneio & Power(2, 1 - 1)) > 0, 1, 0)) as bObsRomaneio1
,convert(bit, iif((ObsRomaneio & Power(2, 2 - 1)) > 0, 1, 0)) as bObsRomaneio2
,convert(bit, iif((ObsRomaneio & Power(2, 3 - 1)) > 0, 1, 0)) as bObsRomaneio3
,max(op.PrevisaoConclusao) as PrevisaoConclusao
into #tempCandidatos
from dbo.PedidosCandidatos t
inner join dbo.pedidos p on p.empresa = t.empresa
and p.numero = t.numero
left join dbo.Clientes_Agendamento ca on ca.codigo_cliente = p.cliente
inner join dbo.TiposPedido pp on pp.Codigo = p.Tipo_Pedido --14/09/15 leonice
inner join dbo.pedidosTotais tp on tp.empresa = t.empresa
and tp.numero = t.numero
inner join dbo.Transp t1 on t1.codigo_transp = p.transportadora
inner join dbo.clientes c on c.codigo_cliente = p.cliente
inner join dbo.Endereco e on e.cliente = p.cliente
and e.sequencia = p.endereco_entrega
inner join itensped ip on ip.Numero = t.numero
and ip.Empresa = t.empresa
left join #tempopitens op on op.empresa = ip.empresa
and op.Item = ip.Item
left join dbo.PedidosLiberaFaturamento pf on pf.idPedido = p.id
left join dbo.vPedidosVidrosMyHome vd on vd.idPedido = p.id
left join TempPedidosEmpenhados pd on pd.empresa = t.empresa
and pd.numero = t.numero
-- adicionar Kasa K à regra para trazer pedidos não programados aqui...
where (
(pd.empresa is null)
or (
(p.marca in (6, 9))
and (p.Data_Programacao is null)
)
)
group by tp.Volumes
,tp.VolumesAcessorios
,pf.idPedido
,tp.VolumesZero
,tp.TVolumes
,tp.TVolumesColetados
,tp.TVolumesAtendidos
,p.Data_Liberacao
,p.Ordem_Compra
,c.Razao_Social
,e.Estado
,e.cidade
,t1.Fantasia
,p.Tipo_Frete
,p.Observacao_1
,p.Observacao_2
,p.Observacao_3
,vd.idPedido
,p.Marca
,p.Transportadora
,pp.Descricao
,t.Empresa
,t.Numero
,ca.Codigo_Cliente
,p.Data_Base
,c.Codigo_Cliente
,p.bObs1
,p.bObs2
,p.bObs3
,ObsRomaneio;
select [id]
,Descricao
into #tempMarcas
from dbo.marcas
where [id] = @Lmarca
union
select m.[id]
,m.Descricao
from dbo.marcas m
inner join dbo.usuarios_marcas um on um.idMarca = m.id
and um.usuario = dbo.fUsuarioKappes()
where @Lmarca = 0
insert into #ProgramacaoPedidosEmLote_PedidosCandidato (
MarcaDescricao
,empresa
,numero
,Prioridade
,Aprovado
,Faturamento
,inclusao
,VidrosMyHome
,DataPrevistaColeta
,Volumes
,VolAcessorios
,LibFinan
,VolumesZero
,TVolumes
,TVolumesColetados
,TVolumesAtendidos
,Data
,Ordem_Compra
,Data_Base
,Razao_Social
,UF
,Cidade
,Transp
,Frete
,OBS1
,OBS2
,OBS3
,PossuiVidro
,TipoPedido
,Marca
,Agendado
,Agendamento
,Estruturas
,Lotes
,PedidoData_Base
,Cliente
,AdicionaEmRomaneio
,Codigo_Transp
,bObs1
,bObs2
,bObs3
,bObsRomaneio1
,bObsRomaneio2
,bObsRomaneio3
,PrevisaoConclusao
)
select distinct m.Descricao as Marca
,pc.*
,t.Volumes
,t.VolAcessorios
,t.LibFinan
,t.VolumesZero
,t.TVolumes
,t.TVolumesColetados
,t.TVolumesAtendidos
,t.Data
,t.Ordem_Compra
,pc.Faturamento as Data_Base
,t.Razao_Social
,t.UF
,t.Cidade
,t.Transp
,t.Frete
,t.OBS1
,t.OBS2
,t.OBS3
,t.PossuiVidro
,t.TipoPedido
,t.Marca
,pa.Agendado
,t.InfAgenda as Agendamento
,pr.Atendido as Estruturas
,'' as Lotes
,PedidoData_Base
,t.Cliente
,1 as AdicionaEmRomaneio
,t.Transportadora
,t.bObs1
,t.bObs2
,t.bObs3
,t.bObsRomaneio1
,t.bObsRomaneio2
,t.bObsRomaneio3
,t.PrevisaoConclusao
from dbo.PedidosCandidatos pc
left join dbo.PrgMetalAtendidos pr on pr.empresa = pc.empresa
and pr.numero = pc.numero
inner join dbo.itensped ip on ip.empresa = pc.empresa
and ip.numero = pc.numero
--inner join dbo.vitenscompostos ic on ic.referencia = ip.item
inner join dbo.vMovel m1 on m1.referencia = ip.item
inner join #tempCandidatos t on t.empresa = pc.empresa
and t.numero = pc.numero
inner join #tempMarcas m on m.[id] = m1.marca
inner join #tempemp pe on pe.empresa = t.empresa
left join [dbo].[Pedidos_Agendamento] pa on pa.empresa = pc.empresa
and pa.numero = pc.numero
-- where (pc.numero = 2258069) or not (suser_sname() = 'KAPPESBERG\\juliano')
order by pc.Prioridade desc
,pc.Faturamento;
update #ProgramacaoPedidosEmLote_PedidosCandidato
set AdicionaEmRomaneio = 0
,MotivoNaoAddRomaneio = 'Não deve gerar Romaneio para pedidos Idelli, My Home e Premium'
where Marca in (2, 6, 11) --Pedidos Idelli, My Home e Premium não deve gerar Romaneio
Responder
03/08/2020
Emerson Nascimento
ainda não compreendo como criar uma tabela temporária com os registros de uma única tabela (ainda por cima sem filtro) possa ser performático.
isto só faz sentido se estiver agrupando dados de várias tabelas.
para acelerar qualquer processo de pesquisa, é preciso que haja índices os campos utilizados como filtro dessa pesquisa (join, where).
com base nas intruções que você publicou, deveriam ser criados os índices da tabela abaixo:
Isso mesmo! Tabelas temporárias também podem (no teu caso, precisam) ter índices.
isto só faz sentido se estiver agrupando dados de várias tabelas.
para acelerar qualquer processo de pesquisa, é preciso que haja índices os campos utilizados como filtro dessa pesquisa (join, where).
com base nas intruções que você publicou, deveriam ser criados os índices da tabela abaixo:
-------------------------------------------+----------------------------------------- | TABELA | INDICE | -------------------------------------------+----------------------------------------- Pedidos | empresa, numero, marca, Data_Programacao -------------------------------------------+----------------------------------------- Clientes_Agendamento | codigo_cliente -------------------------------------------+----------------------------------------- TiposPedido | Codigo -------------------------------------------+----------------------------------------- PedidosTotais | empresa, numero -------------------------------------------+----------------------------------------- Transp | codigo_transp -------------------------------------------+----------------------------------------- Clientes | codigo_cliente -------------------------------------------+----------------------------------------- Endereco | cliente, sequencia -------------------------------------------+----------------------------------------- itensped | Numero, Empresa -------------------------------------------+----------------------------------------- #tempopitens | empresa, Item -------------------------------------------+----------------------------------------- PedidosLiberaFaturamento | idPedido -------------------------------------------+----------------------------------------- vPedidosVidrosMyHome | idPedido -------------------------------------------+----------------------------------------- TempPedidosEmpenhados | empresa, numero -------------------------------------------+----------------------------------------- PedidosCandidatos | Prioridade, Faturamento -------------------------------------------+----------------------------------------- PrgMetalAtendidos | empresa, numero -------------------------------------------+----------------------------------------- vMovel | referencia -------------------------------------------+----------------------------------------- #tempCandidatos | empresa, numero -------------------------------------------+----------------------------------------- #tempMarcas | id -------------------------------------------+----------------------------------------- #tempemp | empresa -------------------------------------------+----------------------------------------- Pedidos_Agendamento | empresa, numero -------------------------------------------+----------------------------------------- #ProgramacaoPedidosEmLote_PedidosCandidato | Marca -------------------------------------------+-----------------------------------------
Isso mesmo! Tabelas temporárias também podem (no teu caso, precisam) ter índices.
Responder
Clique aqui para fazer login e interagir na Comunidade :)