Dicas e melhores práticas SQL

[jothaz]

No atual post prentende-se discutir/exemplificar alguns pontos que poderão serem úteis tanto no entendimento/clareza quanto na performance das queries e stored procedure.

Não são regras, são apenas "dicas" cada caso é um caso. Utilize-as sempre que possível, lembrando que alguns casos poderemos perder em performance para atender determinadas situações.

Esta dicas podem ser aplicadas em quase todos os bancos de dados que utilizem padrão ANSI.

Uso do Select *

Sempre que possível evitar o uso de select * mesmo que a Stored Procedure retorne todos as colunas de uma tabela. Com isso, garante-se a segurança e manutenabilidade da SP, pois sabe-se exatamente quais parâmetros ela retorna assim que se visualiza o seu código.

Uso de > ou >=

Situação: A query é:

select c1,c2 from t where a > 3

e a tabela possui índice em a.. Se houver muitas linhas com a = 3, o engine do banco de dados fará scan de muitas páginas até encontrar a > 3. É mais eficiente se escrita da forma:

select c1,c2 from t where a >= 4

Exists ou Not Exists De maneira geral o uso de EXISTS e IN é mais eficiente que NOT EXISTS e NOT IN. Situação:

if not exists ( select a from t where a = 3 )
                begin .....grupo 1
                end
            else
                begin .....grupo 2
            end

É mais eficiente se escrita da forma

if exists ( select a from t where a =3 )
                begin ... grupo 2
                end 
            else
                begin ... grupo 1
            end

No SELECT de um EXISTS tomar cuidado para não colocar * ou colunas que não sejam índice cluster, pois o EXISTS não necessita retornar colunas somente verificar se a linha existe ou não. Assim, utilizar if exists:

select  1  from t where a > 3

ao invés de if exists:

( select  *  from t where a > 3)

Subqueries com cláusula de outer-join restritiva

select w from outer where y = 1 and x = (select sum(a) from inner where inner.b = outer.z )

Será quebrada pelo bancod e dados nos seguintes passos

select outer.z, summ = sum(inner.a)
                into #work from outer, inner
                where inner.b = outer.z and outer.y = 1
                group by outer.z

            select outer.w
                from outer, #work
                where outer.z = #work.z and outer.y = 1 and outer.x = #work.summ

O bancod de dados copia a cláusula search ( y = 1 ) para a subquery, mas não copia cláusula join. Isto porque copiando a cláusula search, sempre tornará a query mais eficiente, mas copiando a cláusula join pode em muitos casos tornar a query mais lenta. A cópia da cláusula join só é eficiente quando ela é extremamente restritiva, mas o banco de dados faz a quebra antes do otimizador atuar. Então, para tornar a query mais eficiente, conhecendo previamente a alta restritividade da cláusula join, pode-se copiar a cláusula join para a subquery como no exemplo abaixo : tab_x -> tabela grande tab_y -> tabela pequena

select a from tab_x, tab_y
               where tab_x.coluna_valor_unico = tab_y.a and tab_x.b = ( select sum© from tabela_interna where tab_x.d = tabela_interna.e and tab_x.coluna_valor_unico = tab_y.a)

Criação de tabelas dentro de stored procedures Quando uma tabela é criada e utilizada dentro de uma mesma stored procedure, o otimizador não tem conhecimento das suas estatísticas, e assume que esta tabela tem 100 linhas e 10 páginas. Se a tabela criada é muito grande, esta suposição pode levar o otimizador a calcular um plano de acesso não otimizado. Para evitar este problema, crie a tabela em uma procedure e utilize-a em outra. Isto será objeto de especificação durante o Diagrama de Cenários. Variáveis ou parâmetros na cláusula where O otimizador não tem informações sobre o valor de uma variável, mas, em tempo de compilação, sabe o valor de um parâmetro. Isso posto, a utilização de parâmetros em cláusula where, leva o otimizador a produzir um plano de acesso mais eficiente. Por exemplo, a procedure

create procedure s_p1 as declare @x int
        select @x = b1 from t where a1 = 3
        select a from t2 where b1 = @x

    pode ser reescrita como :

    create procedure s_p1 as declare @x int
        select @x = b1 from t where a1 = 3
        exec s_p2 @x

    create s_p2 @x int as select a from t2 where b1 = @x

COUNT x EXISTS Para testes de existência é sempre mais eficiente utilizar EXISTS do que COUNT. Quando se utiliza o COUNT o banco de dados não sabe que se está fazendo um teste de existência e continua pesquisando todas as linhas qualificadas. Já utilizando EXISTS, o banco de dados sabe que é um teste de existência e interrompe a pesquisa quando encontra a primeira linha qualificada. Este mesmo raciocínio é válido quando se utiliza COUNT no lugar de IN ou ANY. OR x UNION O banco de dados não consegue otimizar cláusulas de join ligadas por OR. Neste caso é mais eficiente ligar os conjuntos de resultados por UNION. Por exemplo :

select a from tab1,tab2 where tab1.a = tab2.a OR tab1.x = tab2.x

pode ser reescrito como :

select a from tab1, tab2
                where tab1.a = tab2.a
            UNION
            select a from tab1, tab2
                where tab1.x = tab2.x

A diferença é que na segunda forma, são eliminadas as linhas duplicadas, o que pode ser contornado com UNION ALL. MAX e MIN Agregados O banco de dados utiliza uma otimização especial para MAX e MIN quando há um índice na coluna agregada. Para o MIN a pesquisa é interrompida quando encontra a primeira linha qualificada. Para o MAX, o banco de dados vai diretamente para o final do índice e pega a última linha. Os casos onde estas otimizações especiais não são utilizadas: - a expressão do MAX ou MIN não é uma coluna. - a coluna do MAX ou MIN não é a primeira do índice - existe outro comando agregado na query. - existe uma cláusula de GROUP BY. - se existe cláusula WHERE, a otimização especial de MAX não é utilizada. Se houver possibilidade de se conseguir otimização especial, vale a pena separar em várias queries. É mais eficiente utilizar o índice várias vêzes, do que fazer scan table uma única vez. Em alguns casos, pode ser mais eficiente não utilizar a otimização especial do MIN. Por exemplo, se há uma cláusula where em outro índice, quanto mais restritivo for o WHERE, menos eficiente fica a otimização especial do MIN. A solução é convencer o otimizador a não utilizar a otimização especial do MIN, colocando, por exemplo, duas agregações na query. Por exemplo :

select MIN(coluna1)
            from tab where coluna2 = <valor encontrado só no final do índice da coluna1>

O banco de dados utilizará aqui a otimização especial do MIN, e fará um scan em quase todo o índice, pois a qualificação na cláusula WHERE força esta situação. Se colocarmos mais um aggregate, convenceremos o otimizador a utilizar o processo normal, criando um plano de acesso pelo índice da coluna2, neste caso, mais eficiente que a otimização especial do MIN.

select MIN(coluna1), MAX[coluna2)
            from tab where coluna2 = <valor encontrado só no final do índice da coluna1>

Joins e Datatypes Se a cláusula join utiliza datatypes diferentes, um deles será convertido para o outro. O datatype convertido é o hierarquicamente inferior. O otimizador não consegue escolher um índice na coluna que é convertida. O ideal é evitar este tipo de join, mas se não for possível, pode-se explicitamente converter o lado do join que tem o menor custo de não utilização do índice. Por exemplo :

select c1,c2 from tab1, tab2
                 where tab1.col_char_75 = convert ( char(75), tab2.col_varchar_75 )

Parâmetros e Datatypes O otimizador pode utilizar o valor de um parâmetro para calcular o custo de um plano. Mas o banco de dados não poderá utilizar valores de parâmetros convertidos. O ideal então é garantir que o parâmetro tem o mesmo datatype da coluna com a qual ele será comparado. Por exemplo :

create procedure s_p @x varchar(30) as select c1,c2 from tab where coluna_char_30 = @x

pode ser reescrito como:

create procedure s_p @x char(30) as select c1,c2 from tab where coluna_char_30 = @x

Testar retorno de comando SQL Após um comando SQL (select, insert, update, delete) sempre é necessário testar duas variáveis para um correto processamento da Stored Procedure: @@error (código de retorno do banco de dados ) e @@rowcount (quantidade de linhas retornadas pelo comando). Estas variáveis devem ser testadas para que a SP possa dar como retorno os códigos de erro -1001 (erro banco de dados ) e -1002 (não encontrou registro). No caso do raiserror, retornar 40001 e 40002 respectivamente. Para testar siga o exemplo:

declare @erro smallint,
              @numlin smallint
     
        select a from t where a > 3

        /* sempre salvar o valor de @@error em uma variável pois para cada 
                           comando SQL o  banco de dados  gera um @@error. */

        select @erro = @@error        
              @numlin = @@rowcount
    
        if @erro != 0
                      return -1001             /* erro  banco de dados  */

               if @numlin = 0
                   return -1002            /*  não encontrou registro */

Stored Procedure chamadora Para as SP´s consideradas chamadoras, ou seja, aquelas que chamam outras SP´s deve-se tomar um cuidado especial com os códigos de retorno (vide quadro de códigos). Além dos códigos padronizados (0, -1001, -1002, ...) a SP ainda pode retornar códigos próprios dela durante a execução (códigos -1 a -14). Estes códigos sinalizam que houve algum tipo de problema para a execução da SP: não achou a SP, não existe permissão para executá-la, entre outras. Testar na SP chamadora estes códigos de retorno também. Exemplo:

create procedure s_teste
            as declare @return int        /* p/ código de retorno */

                /* executa s_teste2  buscando seu código de retorno */

                execute @return = s_teste2   

                /* testa retorno de s_teste2 */
                
                if @return != 0
                     begin raiserror  40001
                    return -1001  /* erro  banco de dados  */
                   end 
                else
                   begin raiserror 40000
                    return 0    /* OK */ 
                   end

Utilização do comando RAISERROR

O uso do RAISERROR no código é obrigatório para as SP´s chamadoras ou que retornem múltiplas todos´s da mesma tabela ou de tabelas diferentes. Isto faz-se necessário para facilitar e viabilizar o controle de erros nas aplicações PowerBuilder. O RAISERROR deve ser colocado antes de todos os return´s do código da SP. Ver padronização dos códigos no quadro CÓDIGOS DE RETORNO. Nas demais SP´s fica a critério do coordenador de cada equipe de desenvolvimento, a decisão pela obrigatoriedade da utilização do RAISERROR.

Exemplo: vide código SP do item acima.

Uso de campos formato DATA (smalldatetime e datetime)

Tomar bastante cuidado com a utilização de campos no formato DATA, principalmente para utilização de datas zeradas e datas com 99999999. No banco de dados este conceito perdeu o sentido pois só são aceitas datas válidas para um campo formato DATA.

Assim, utilizar

1900/01/01 para representar DATA = 0

e

2070/12/31 para representar DATA = 99999999.

Estas datas são os limites abaixo e acima para os campos smalldatetime. Para casos aonde este range não seja suficiente, buscar orientação com o Suporte/Adm. de Dados.

Fonte/Autor/link

AUTOR: estas dicas foram retiradas da internet, livros e dos DBA`s nas empresa que trabalhei. Consequentemente foram alteradas, corrigidas e adicionadas ea gora estou compartilhando com os foristas. Caso alguém seja o autor favor postar o link para que os créditos possam ser atribuido.

Dúvidas, criticas, contribuições, correções e adições serão bem vindas.