none
Performance montando clause where na store procedure - Query dinâmica RRS feed

  • Pergunta

  • Olá grupo.

    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
    domingo, 22 de março de 2015 16:41

Respostas

Todas as Respostas