Tenho consultas feitas com store procedure.
A aplicação passa os parâmetros e monto a cláusula where na SP, conforme os campos preenchidos.
Se montar o where apenas com os campos preenchidos, a árvore de decisão não fica compilada porque o filtro só é conhecido dentro do EXEC.
Para não usar o EXEC e deixar a query inteira compilada, a opção é verificar se cada parâmetro é diferente de null e vazio junto com a comparação com o campo da tabela.
Com isso o filtro compara todos os campos, o que eu acredito que deixe a consulta mais lenta.
Gostaria de saber qual dessas duas maneiras é a melhor em termos de custo computacional para o banco.
Será esse um caso onde mais linhas fica mais rápido?
Não usei os exemplos com paginação no banco, para não extender muito o assunto.
O usuário do banco está com idioma inglês e as datas e números são passadas nesse formato.
Antes de abrir a conexão, valido todos os campos alertando o usuário.
Segue exemplos:
1 - Montando o filtro somente com os campos preenchidos:
Aqui o código fica maior, mas no filtro em tempo de execução tem menos comparações.
Quando é preenchido as duas datas, inicial e final, consigo usar o BETWEEN.
CREATE PROCEDURE SP_Cargo_Listar
@id SMALLINT=NULL,
@nome VARCHAR(30)='',
@idUsuI INT=NULL,
@nmUsuI VARCHAR(50)='',
@dtII DATETIME=NULL,
@dtIF DATETIME=NULL,
@idUsuA INT=NULL,
@nmUsuA VARCHAR(50)='',
@dtAI DATETIME=NULL,
@dtAF DATETIME=NULL
AS
DECLARE
@strWhere VARCHAR(1000)
SET @strWhere=''
SET @nome=REPLACE(UPPER(LTRIM(RTRIM(@nome))),'''','''''')
SET @nmUsuI=REPLACE(UPPER(LTRIM(RTRIM(@nmUsuI))),'''','''''')
SET @nmUsuA=REPLACE(UPPER(LTRIM(RTRIM(@nmUsuA))),'''','''''')
IF(@id IS NULL)
BEGIN
IF(@nome <> '')
BEGIN
SET @strWhere=' AND UPPER(c.nome) LIKE ''%' + @nome + '%'''
END
IF(@idUsuI IS NOT NULL)
BEGIN
SET @strWhere=@strWhere + ' AND c.idUsuI=' + CAST(@idUsuI AS VARCHAR)
END
IF(@nmUsuI<>'')
BEGIN
SET @strWhere=@strWhere + ' AND UPPER(ui.nome) LIKE ''%' + @nmUsuI + '%'''
END
IF ISDATE(@dtII)=1 AND ISDATE(@dtIF)=1
BEGIN
SET @strWhere=@strWhere + ' AND (c.dtI BETWEEN ''' + CONVERT(VARCHAR,@dtII,101)
+ ' 00:00'' AND ''' + CONVERT(VARCHAR,@dtIF,101) + ' 23:59'')'
END
ELSE
IF ISDATE(@dtII)=1
BEGIN
SET @strWhere=@strWhere + ' AND c.dtI>='''
+ CONVERT(VARCHAR,@dtII,101) + ' 00:00'''
END
ELSE
IF ISDATE(@dtIF)=1
BEGIN
SET @strWhere=@strWhere
+ ' AND c.dtI<=''' + CONVERT(VARCHAR,@dtIF,101) + ' 23:59'''
END
IF(@idUsuA IS NOT NULL)
BEGIN
SET @strWhere=@strWhere + ' AND c.idUsuA=' + CAST(@idUsuA AS VARCHAR)
END
IF(@nmUsuA<>'')
BEGIN
SET @strWhere=@strWhere + ' AND UPPER(ua.nome) LIKE ''%' + @nmUsuA + '%'''
END
IF ISDATE(@dtAI)=1 AND ISDATE(@dtAF)=1
BEGIN
SET @strWhere=@strWhere + ' AND (c.dtA BETWEEN ''' + CONVERT(VARCHAR,@dtAI,101)
+ ' 00:00'' AND ''' + CONVERT(VARCHAR,@dtAF,101) + ' 23:59'')'
END
ELSE
IF ISDATE(@dtAI)=1
BEGIN
SET @strWhere=@strWhere + ' AND c.dtA>='''
+ CONVERT(VARCHAR,@dtAI,101) + ' 00:00'''
END
ELSE
IF ISDATE(@dtAF)=1
BEGIN
SET @strWhere=@strWhere
+ ' AND c.dtA<=''' + CONVERT(VARCHAR,@dtAF,101) + ' 23:59'''
END
END
ELSE
BEGIN
SET @strWhere=' AND c.id=' + CAST(@id AS VARCHAR)
END
IF(@strWhere <> '')
BEGIN
SET @strWhere=SUBSTRING(@strWhere,5,LEN(@strWhere))
SET @strWhere=' WHERE ' + @strWhere
END
EXEC
('
SELECT
c.id,
c.nome,
c.idUsuI,
ui.nome AS nmUsuI,
CONVERT(VARCHAR(10),c.dtI,103) + '' '' + CONVERT(VARCHAR(8),c.dtI,108) AS dtI,
c.idUsuA,
ua.nome AS nmUsuA,
CONVERT(VARCHAR(10),c.dtA,103) + '' '' + CONVERT(VARCHAR(8),c.dtA,108) AS dtA
FROM Cargo c
INNER JOIN Fun ui ON c.idUsuI=ui.id
LEFT JOIN Fun ua ON c.idUsuA=ua.id'
+ @strWhere +
' ORDER BY c.nome'
)
RETURN
GO
2 - Comparando todos os campos
Aqui o código é menor, porém com mais comparações no filtro, e não consigo usar o BETWEEN nas datas.
CREATE PROCEDURE SP_Cargo_Listar
@id SMALLINT=NULL,
@nome VARCHAR(30)='',
@idUsuI INT=NULL,
@nmUsuI VARCHAR(50)='',
@dtII DATETIME=NULL,
@dtIF DATETIME=NULL,
@idUsuA INT=NULL,
@nmUsuA VARCHAR(50)='',
@dtAI DATETIME=NULL,
@dtAF DATETIME=NULL
AS
DECLARE
@strWhere VARCHAR(1000)
SET @strWhere=''
SET @nome=REPLACE(UPPER(LTRIM(RTRIM(@nome))),'''','''''')
SET @nmUsuI=REPLACE(UPPER(LTRIM(RTRIM(@nmUsuI))),'''','''''')
SET @nmUsuA=REPLACE(UPPER(LTRIM(RTRIM(@nmUsuA))),'''','''''')
SELECT
c.id,
c.nome,
c.idUsuI,
ui.nome AS nmUsuI,
CONVERT(VARCHAR(10),c.dtI,103) + ' ' + CONVERT(VARCHAR(8),c.dtI,108) AS dtI,
c.idUsuA,
ua.nome AS nmUsuA,
CONVERT(VARCHAR(10),c.dtA,103) + ' ' + CONVERT(VARCHAR(8),c.dtA,108) AS dtA
FROM Cargo c
INNER JOIN Fun ui ON c.idUsuI=ui.id
LEFT JOIN Fun ua ON c.idUsuA=ua.id
WHERE
(@id IS NULL OR c.id=@id) AND
(@nome = '' OR UPPER(c.nome) LIKE '%'+@nome+'%') AND
(@idUsuI IS NULL OR c.idUsuI=@idUsuI) AND
(@nmUsuI = ''OR UPPER(ui.nome) LIKE '%'+@nmUsuI+'%') AND
(@dtII IS NULL OR c.dtI>=@dtII + ' 00:00') AND
(@dtIF IS NULL OR c.dtI<=@dtIF + ' 23:59') AND
(@idUsuA IS NULL OR c.idUsuA=@idUsuA) AND
(@nmUsuA = '' OR UPPER(ua.nome) LIKE '%'+@nmUsuA+'%') AND
(@dtAI IS NULL OR c.dtA>=@dtAI + ' 00:00') AND
(@dtAF IS NULL OR c.dtA<=@dtAF + ' 23:59')
ORDER BY c.nome
GO
