Eu tenho um procedimento armazenado definido pelo usuário Ao chamar sem passar valores explícitos, desejo apenas passar todos os locais (nvarchar(50)), que é o campo de chave primária de uma tabela: Monitor_Locations (com ~ 850 entradas)
Uma parte do SP é definida da seguinte forma (recortada).
ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
@Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
,@rangeStart as DateTime = '1970-01-01'
,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
INSERT INTO @loclist(Location)
SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
INSERT INTO @loclist(Location)
SELECT
ML.Location
FROM Monitor_Locations as ML join
tvf_splitstring(@Locations) as ss ON
ML.Location=ss.Item OR
ML.Location like ss.Item+'[_]%'
ORDER BY ML.Location;
With Deploys as (
SELECT
D.Location,
MIN(D.Start) as Start,
MAX(D.[Stop]) as Stop
FROM
Deployments as D
WHERE
D.Stop is not NULL
)
...fazer um monte de outras coisas...
para melhorar a velocidade do procedimento armazenado quando uma lista restrita de sites é enviada para o SP, eu queria substituir a cláusula WHERE por
WHERE
CASE
WHEN D.Stop IS NULL THEN 0
WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
END=1
mas onde antes o SP levava de 6 a 8 segundos para ser executado, agora leva 2,5 minutos (para chamadas sem uma lista restritiva). Achei que levaria aproximadamente a mesma quantidade de tempo em cada sentido para a lista completa, pois a segunda cláusula do CASE deveria ser disparada muito rapidamente e a terceira cláusula nunca deveria ser examinada.
Então o que está acontecendo? Este código:
WHERE
CASE
WHEN D.Stop IS NULL THEN NULL
WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --else null
END is not null
Leva um tempo de execução de aproximadamente 10 minutos com este plano:
Para contrastar, aqui está o WHERE D.Stop is not NULLplano (6s):
Em um ponto, este SP estava demorando 1 segundo com esta versão, mas mudando o SP e voltando novamente, demorou 6 segundos novamente. Conforme mencionado nas respostas, isso provavelmente ocorreu devido à detecção de parâmetros.
tempos de execução
Meu objetivo de tempo de execução é inferior a 2 segundos, pois este será um SP executado com frequência em um aplicativo da Web que o usa para preencher e restringir outras seleções do usuário. Basicamente, não quero que isso seja um gargalo perceptível. Os tempos iniciais de execução foram da ordem de 3 minutos, mas depois de adicionar ou alterar alguns índices, caiu para a faixa de 6 a 8 segundos.
Na segunda-feira (2016-08-29), antes de grandes alterações WHERE simples sem parâmetros de entrada: 5s WHERE simples com rangeStart e rangeEnd: 4s WHERE simples com @Locations definido como uma variável CSV de 7 elementos CASEd WHERE: até 10 minutos
Depois de retrabalhar a função CLR (veja minha resposta abaixo) Terça-feira (30/08/2016) Simples ou CASEd WHERE sem parâmetros de entrada OU Simples ou CASEd WHERE com rangeStart e rangeEnd: 3s Simples ou CASEd WHERE com 7 elementos @Locations: 0 -1s
Depois de migrar a variável de tabela @loclist para a tabela temporária #loclist Todos os WHEREs/parâmetros testados: 0-1s
