Blog – Fabiano Neves Amorim

Hoje um colega aqui da empresa, precisava de uma função que
retorne a quantidades que um caracter aparece em uma determinada string.

Eu desconheço uma função do SQL que faça isso, portanto sugeri
que ele criasse uma user function para fazer essa validação.

Ai é que ta, sabemos que user functions podem dar uma boa
dor de cabeça pela sua péssima performance quando utilizadas em consultas que
retornam uma quantidade razoável de registros. Então, parei para pensar melhor para
ver qual seria a melhor maneira de fazer essa function.

Creio que a idéia inicial de todos seria fazer um loop na string,
correndo por todos os caracteres somando a quantidade de vezes em que o
caracter aparece na string. Algo mais ou menos assim:

DECLARE @Str VarChar(200),

       
@Caracter_A_Procurar VarChar(200),

        @i Int,

       
@Qtde_Caracter Int;

SET @Str = ‘Um teste para validar
quantos caracteres existem nesta String’

SET @Caracter_A_Procurar = ‘a’

SET @i = 0

SET @Qtde_Caracter = 0

WHILE @i <= LEN(@Str)

BEGIN

    IF SUBSTRING(@Str, @i, 1) = @Caracter_A_Procurar

        SET @Qtde_Caracter =
@Qtde_Caracter + 1

    SET @i = @i + 1

END

SELECT @Qtde_Caracter

Ok, o código acima funciona, mas pensando bem, existe outra
maneira bem mais eficiente de fazer esta validação. Que tal assim:

DECLARE @Str VarChar(200),

       
@Caracter_A_Procurar VarChar(200)

SET @Str = ‘Um teste para validar
quantos caracteres existem nesta String’

SET @Caracter_A_Procurar = ‘a’

SELECT LEN(@Str) – LEN(REPLACE(@Str,
@Caracter_A_Procurar, ”))

O código acima pega a string e faz um replace do caracter “a”
por nada, e depois verifica a quantidade de caracteres da string sem o “a” e
subtrai pela quantidade de caracteres original. O resultado será exatamente a
quantidade de vezes em que “a” aparece na string.

Fica ai a dica…

Para quem não leu o 1º Post, segue o link.

Quiz – Porque o SQL não utiliza meu indice?

É isso ai pessoal, vamos entender melhor porque o SQL não pode confiar no índice para retornar nossas informações.

Para entender melhor este caso, temos que pensar como se fossemos o Otimizador de consultas.

Seguindo nessa lógica, vamos lá.

Lembrando que o papel do Otimizador é de calcular qual é a maneira mais rápida de retornar os dados de uma determinada consulta, baseado nas informações que ele tem em relação ao schema da tabela, estatísticas, e recursos de hardware disponíveis no momento da execução.

Bom, no caso de nossa tabela, o Otimizador sabe que ele tem apenas 2 maneiras de acessar os dados, são elas: Utilizar o Índice Cluster, ou utilizar o Índice NonCluster. Os dois índices tem todos os dados necessários para retornar a consulta gravados em suas páginas.

O ponto chave aqui é, qual é o melhor índice o Cluster que esta ordenado por ID, ou o NonCluster que esta ordenado por Nome1 e Valor1.

De primeira vista, podemos olhar o nonclustered e dizer que ele sem dúvida é a melhor maneira de acessar os dados, já que ele está ordenado por Nome1 e Valor1, que são exatamente as colunas utilizadas na clausula WHERE da consulta. Porém, existe um fator determinante para que esta escolha não seja viável, a clausula ORDER BY que está solicitando que os dados sejam retornados pela coluna ID.

Bom mas você pode estar pensando, ora, a coluna ID pertence ao índice cluster, então porque não pegar ela e retornar os dados? A resposta é: – Porque o SQL não pode confiar que os dados estarão na ordem correta. E neste caso ele teria ler os dados do índice e depois fazer um SORT pela coluna ID. Isso pode ser comprovado caso nós forcemos o uso do índice. Vamos ver o como ficaria o plano de execução.

SELECT ID

  FROM TabTeste WITH(INDEX = ix_TesteSem_Include)

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Como podemos observar, ao forçar o uso do Índice o SQL gera um operador de SORT onde este processo representa 82% de custo, quase que o total da consulta.

Ok, entendido que o SQL não pode usar o índice noncluster, porque neste caso ele teria que ordenar o resultado por ID, mas, porque ele não pode confiar no índice? Os dados não estão no Índice?

O que acontece é que o índice está ordenado por Nome1,Valor1 e ID, e fazendo esta leitura o SQL não pode confiar que os registros retornados estarão na ordem correta. Perai, ficou meio confuso né? Vamos fazer o seguinte, vamos ver na prática, acho que vai ficar mais fácil de entendermos.

Vamos ver como o índice está ordenado fisicamente nas páginas de dados.

Primeiro temos que pegar o valor Hexadecimal que diz qual é a primeira página de dados do índice.

select * from sysindexes

where id = Object_id(‘TabTeste’)

O valor da coluna root contem o ponteiro para a primeira página raiz do índice.

Vamos converter o valor Hexadecimal para um Inteiro. Basta reajustar a ordem dos valores e usar a função CAST do próprio SQL.

select CAST(0x040D AS INT)

Ok, então sabemos que a página raiz do índice Nonclustered é o 1037, vamos ver como os dados estão armazenados nesta página? Para isso vamos usar o DBCC PAGE. Os parâmetros passados são,

2 = ID do banco de dados, como estou no TempDB o valor será sempre 2. Caso você esteja logado em outro banco pode pegar usando select DB_ID(DB_name())

1 = Arquivo físico onde a página está armazenada, no Hexadecimal os 4 últimos valores irão dizer qual é o arquivo, no nosso exemplo foi retornado 0100 fazendo a reorganização dos valores teríamos 0001, portanto 1.

1037 = Página onde os dados estão localizados

3 = Forma de visualização dos dados da página. Pode ser 1, 2 ou 3. Como quero ver os dados em uma grade usei o 3.

DBCC TRACEON(3604)

GO

DBCC PAGE(2,1,1037,3)

Ok estes são os dados da página nível raiz, vamos descer os níveis, para ver os dados… só lembrando… A arquitetura do índice é…

Na coluna ChildPageID temos qual é a página filho do valor atual, vamos analisar a primeira página de dados que é a  PageID 1034. Nesta página teremos os dados do índice, e poderemos aplicar o filtro do where.

DBCC PAGE(2,1,1034,3)

.

.

.

Repare que temos o valores dos dados gravados no índice, e eles estão corretamente ordenados por Nome1, Valor1, e ID que é a chave do índice Cluster. Se você continuar a ler os valores das páginas 1036 e 1038 verá que a tabela inteira estará armazenada e ordenada conforme requerimento de nossa consulta(Nome1 e Valor1 para o filtro, e ID para ordenação).

Vamos ver o final do índice, que é a página 1038.

DBCC PAGE(2,1,1038,3)

Sendo assim podemos facilmente visualizar que para a consulta abaixo o SQL poderia simplesmente ler os dados na ordem em que os valores estão sendo apresentados, que ele iria retornar tudo certo, inclusive os dados já estão ordenados por ID. Na teoria, não precisa do SORT, gerado quando forçamos o uso do índice.

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Porque então não fazer isso? Votando a pensar como o Otimizador, me responda uma coisa, baseado nas informações que você tem, schema, estatísticas.. Você realmente pode confirmar que SEMPRE os dados estarão conforme nossa visualização? Sim? Talvez? Não sei? …

Caso o SQL pense que o índice já está ordenado corretamente e não gere o SORT, O que aconteceria se eu por ex, incluísse uma linha com os seguintes  valores?

SET IDENTITY_INSERT TabTeste ON

INSERT INTO TabTeste(ID, Nome1, Valor1) VALUES(-1, ‘aaaa’, 1)

SET IDENTITY_INSERT TabTeste OFF

Para ver o dano, vamos rodar a consulta mas sem o ORDER BY, apenas para tentar forçar que o SQL use o Índice, ou seja, você quer trazer os dados pela ordem em que eles estão armazenados no índice,

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

Como podemos observar os dados não foram retornados na ordem esperada, ou seja, ordenados por ID, se o SQL ler os dados do índice ele terá que gerar um sort para reordenar os valores por ID.

Vamos ver como os dados ficaram na última página do índice.

DBCC PAGE(2,1,1038,3)

Podemos ver que, a tabela é ordenada Nome1 e Valor1, como o Valor1 que foi inserido é “1” então ele deve vir depois do ultimo “0”, mas o ID “-1” é menor que “1000”. Seguindo a ordem, a regra é primeiro por Nome1 e depois por Valor1, e não por Nome1 e depois por ID.

Fiz essa inclusão para mostrar que o SORT é necessário já que no índice nonclustered a coluna ID não está ordenada conforme desejamos.

Se alterarmos a consulta para trazer apenas um Nome1 e apenas 1 Valor1, então o índice é utilizado, e não é mais necessário o SORT, pois o Otimizador já sabe que ele pode confiar que para os valores nome1 = ‘aaaa’ e valor1 = 0, os registros estarão ordenados corretamente por ID.

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 = 0

 ORDER BY ID

Com isso podemos aprender que o Otimizador pode optar por, evitar fazer um SORT mesmo que isso cause uma não utilização de um índice. Operações de SORT acontecem no TEMPDB e são extremamente pesadas.

Ao criar um índice utilizando a opção Include, o SQL Server grava os valores do INCLUDE depois das chaves do índice, ou seja, trocamos a ordem de Nome1, Valor1, ID por Nome1, ID, Valor. Fazendo isso o SQL pode ter certeza de que os valores estarão ordenados por ID, já que o filtro no Nome1 é uma constante ou seja, é um valor único.

CREATE NONCLUSTERED INDEX ix_Teste_Include ON TabTeste(Nome1) INCLUDE(Valor1)

GO

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Repare que para a consulta que utiliza o índice com Include, o valor passado como SeekPredicate para o Índice foi o Nome1 = ‘aaaa’, já o Valor1 <= 10 é passado como Predicate, ou seja, ele não será utilizado para fazer o seek no índice. Depois eu o seek por Nome1 for feito, será aplicado um filtro neste resultado para que apenas as linhas onde Valor1 <= 10 sejam retornadas.

O Gustavo Maia fez uma analise bem interessante em relação a estes testes, segue o link para o doc que ele escreveu.

http://cid-52eff7477e74caa6.skydrive.live.com/self.aspx/Publica/Gustavo%20Maia%20-%20Desafio.doc

Bom pessoal, semana que vem estou de férias, portanto que Deus Abençoe a todos e tenham um Feliz Natal.

Abraço.

Afe, cada uma viu… a WTF da vez veio novamente do forum MSDN.

http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=4235086&SiteID=21

Eu fico feliz quando vejo isso, é sinal que vou ter emprego garantido por um bom tempo.

Desta vez tem de tudo, código comentado no meio do SQL, SubQueries totalmente desnecessárias… (Campo =1 OR Campo IS NULL)… Bom… veja com os próprios olhos…

UPDATE CIRCUITO SET
    cod_coord_implantacao_cir  = (SELECT cod_loc FROM localidade WHERE cnl_loc=cod_coordenador_implantacao),
    cod_locA_cir    = (SELECT cod_loc FROM localidade WHERE cnl_loc=sigla_localidade_ponta_a),
    cod_locB_cir    = (SELECT cod_loc FROM localidade WHERE cnl_loc=sigla_localidade_ponta_b),
    cod_orgven_cir   = (SELECT cod_orgven FROM Orgao_Vendas WHERE sigla_orgven=cod_coordenador_venda),
    conta_corrente_usr_cir   = cod_conta_corrente,
/*    contrato_cir   = ISNULL(cod_contrato_usuario_circuito,contrato_cir), */
    contrato_especial_cir     = cod_contrato_especial,
    data_contrato_cir  = ISNULL(data_contrato,data_contrato_cir),
    data_cadastro_cir  = data_entrada_contrato,
    data_prazo_entrega_cir      = case  when data_prazo_entrega_cir is null then data_previsao_ativacao
     when data_prazo_entrega_cir <> cast(data_previsao_ativacao as datetime) then data_previsao_ativacao
      else data_prazo_entrega_cir
      end,
    data_prazo_entrega_original_cir =  case when final_stat = 1 then null
     else
         case when data_prazo_entrega_cir is not null
      and data_prazo_entrega_cir < cast(data_previsao_ativacao as datetime)
      and data_prazo_entrega_original_cir is null then data_prazo_entrega_cir
         else data_prazo_entrega_original_cir
         end
     end,
    data_primeiro_reaprazamento_cir =  case when final_stat = 1 then null
     else
         case when data_prazo_entrega_cir is not null
      and data_prazo_entrega_cir < cast(data_previsao_ativacao as datetime)
      and data_prazo_entrega_original_cir is not null
      and data_primeiro_reaprazamento_cir is null then data_prazo_entrega_cir
         else data_primeiro_reaprazamento_cir
         end
     end,
    data_alt_data_prazo_entrega_cir = case when final_stat = 1 then null
     else data_alt_data_prazo_entrega_cir
     end,
    resp_alt_data_prazo_entrega_cir = case when final_stat = 1 then null
     else resp_alt_data_prazo_entrega_cir
     end,
    contrato_cir  = cod_contrato_usuario_circuito,
    cod_pendA_cir   =  (SELECT cod_pend FROM Pendencia WHERE sigla_pend=cod_pendencia_ponta_a),
    cod_pendB_cir   =  (SELECT cod_pend FROM Pendencia WHERE sigla_pend=cod_pendencia_ponta_b),
    cod_pendpri_cir   =  (SELECT cod_pendpri FROM Pendencia_Prioritaria WHERE cod_origem_pendpri=cod_pendencia_prioritaria),
    cod_ofen_cir    =  (SELECT cod_ofen FROM Ofensor WHERE sigla_ofen=cod_ofensor_circuito),
    cod_veloc_cir   =  (SELECT cod_veloc FROM Velocidade WHERE valor_veloc=cod_velocidade),
    data_disp_tec_cir   = DATA_DISPONIBILIDADE_TECNICA,
    data_tec_sem_previsao_cir  = DATA_INVIABILIDADE_TECNICA,
    cod_provA_cir   = (SELECT cod_prov FROM Provedor WHERE cod_prov=COD_PROVEDOR_PONTA_A),
    num_pedido_acessoA_cir  = NUM_PEDIDO_ACESSO_PONTA_A,
    data_pedido_acessoA_cir  = DATA_PEDIDO_ACESSO_PONTA_A,
    cod_tipacesA_cir  = (SELECT cod_tipaces FROM Tipo_Acesso WHERE sigla_tipaces=TIPO_ACESSO_PONTA_A),
    num_acessoA1_cir   = NUM_ACESSO_PONTA_A,
    data_aceiteA_cir   = DATA_ACEITE_PONTA_A,
    enderecoA_cir   = DESCR_ENDERECO_PONTA_A,
    cod_provB_cir   = (SELECT cod_prov FROM Provedor WHERE cod_prov=COD_PROVEDOR_PONTA_B),
    num_pedido_acessoB_cir  = NUM_PEDIDO_ACESSO_PONTA_B,
    data_pedido_acessoB_cir  = DATA_PEDIDO_ACESSO_PONTA_B,
    cod_tipacesB_cir  = (SELECT cod_tipaces FROM Tipo_Acesso WHERE sigla_tipaces=TIPO_ACESSO_PONTA_B),
    num_acessoB1_cir   = NUM_ACESSO_PONTA_B,
    data_aceiteB_cir   = DATA_ACEITE_PONTA_B,
    enderecoB_cir   = DESCR_ENDERECO_PONTA_B,
    terminal_cir    = COD_NUM_TERMINAL,
    data_alocacao_cir   = DATA_FIN_ALOCACAO,
    data_configuracao_cir  = DATA_FIN_CONFIGURACAO,
    indic_reaprov_facilA_cir  = INDIC_REAPR_ACESSO_PONTA_A,
    indic_reaprov_facilB_cir  = INDIC_REAPR_ACESSO_PONTA_B,
    indic_situacao_pendencia_cir  = INDIC_SITUACAO_PENDENCIA,
    indic_tipo_pendencia_cir  = INDIC_TIPO_PENDENCIA,
    cod_agente_execa_cir = (case when final_stat = 1 then null
            else cod_agente_execa_cir
                         end),

 

    cod_agente_execb_cir = (case when final_stat = 1 then null
            else cod_agente_execb_cir
                         end),

 

    cod_agente_coord_cir = (case when final_stat = 1 then null
            else cod_agente_coord_cir
                         end),

 

    cod_acao_cir   = (CASE indic_tipo_pendencia
          WHEN 1 THEN 1
      WHEN 5 THEN 1
      WHEN 2 THEN CASE indic_acao
        WHEN ‘F’ THEN 2
        WHEN ‘V’ THEN 3
        WHEN ‘E’ THEN 4
        ELSE 6
       END
    END),
/*
    cod_stat_cir   = (CASE indic_situacao_pendencia
     WHEN 1 THEN
      CASE
       WHEN ((cod_tiporgven_orgven=2)
       AND (DATEDIFF(DAY,GETDATE(),DATA_PRAZO_ENTREGA_CIR)>30)
       AND (DATA_PRAZO_ENTREGA_CIR IS NOT NULL)) THEN 12
       WHEN ((cod_tiporgven_orgven=1)
       AND (DATEDIFF(DAY,GETDATE(),DATA_PRAZO_ENTREGA_CIR)>45)
       AND (DATA_PRAZO_ENTREGA_CIR IS NOT NULL)) THEN 12
      ELSE
       1 — PENDENTE
         END
     WHEN 2 THEN 3 — DISPONIBILIZADO
     WHEN 3 THEN 2 — INVIAVEL
     WHEN 4 THEN 9 — INTERROMPIDO
     WHEN 5 THEN 14 — ATIVADO COMERCIAL
        END),
*/
    cod_stat_cir   = (CASE indic_situacao_pendencia
     WHEN 1 THEN
      CASE
       WHEN ( (DATEDIFF(DAY,GETDATE(),DATA_PRAZO_ENTREGA_CIR)>30)
       AND (DATA_PRAZO_ENTREGA_CIR IS NOT NULL)) THEN 12
      ELSE
       1 — PENDENTE
         END
     WHEN 2 THEN 3 — DISPONIBILIZADO
     WHEN 3 THEN 2 — INVIAVEL
     WHEN 4 THEN 9 — INTERROMPIDO
     WHEN 5 THEN 14 — ATIVADO COMERCIAL
        END),
   cod_veloc_anterior_cir= (SELECT cod_veloc FROM Velocidade WHERE valor_veloc=veloc_anterior),
   indic_tipo_servico= CP.indic_tipo_servico,
   cod_degrau= CP.cod_degrau,
   data_aditivo_cir=CP.data_aditivo_cir,
   indic_tipo_cliente_cir = CP.indic_tipo_cliente_cir,
   migrado_cir = CP.migrado,
   sinergia_novo = CP.sinergia_novo,
   backbone_acesso = CP.backbone_acesso,
   proposta_claro = CP.proposta_claro,
   designacao_associada = CP.designacao_associada,
   solicitacao_associada = CP.solicitacao_associada,
   designacao_claro = CP.designacao_claro,
   projeto_claro = CP.projeto_claro,
   data_inicio_faturamento_claro = CP.data_inicio_faturamento_claro
    FROM Circuito_Pendentes CP, status
    WHERE (ots_ativacao_vsat_cir=ots_ativacao_vsat)
    and (cod_stat = cod_stat_cir)
    and (cod_servico=300)
    and (cod_ser_cir=300)
    and (fonte_cir=0)
    and (cod_acao_cir  <> 7 or cod_acao_cir is null)  –Ação Desativar

Quer melhorar um pouco a performance de abertura do SQL Server Management Studio?

Para isso vai ter que fazer uma coisa que os desenvolvedores não gostam muito, bom, eu não gosto, rs.

Remover o splash screen.

Para fazer isso basta adicionar o parâmetro –nosplash no comando de abertura do .exe, por ex:

Meu atalho ficou como a imagem acima, deu uma diferença razoável no tempo de abertura.

A mesma lógica também funciona para o VS e o BIDS.

Mais uma da série WTF?

Dessa vez vi em um forum aqui do MSDN…
Lindo eim, esses INs….

SELECT DISTINCT Dad.TBDadosFatura_Numero, Dad.TBDadosFatura_Nome, Dad.TBDadosFatura_DataEntrada, Hon.TBAmb_Descricao, Dad.TBDadosFatura_ValorTotal

FROM TBDadosFatura Dad

INNER JOIN TBLHonorarios Hon ON Dad.GuiaNumero = Hon.GuiaNumero

WHERE (Hon.TBCentroCusto_Codigo IN (‘0100003’)

OR Hon.TBCentroCusto_Codigo IN (‘0100062’)

OR Hon.TBCentroCusto_Codigo IN (‘0100064’)

OR Hon.TBCentroCusto_Codigo IN (‘0100031’)

OR Hon.TBCentroCusto_Codigo IN (‘0100032’)

OR Hon.TBCentroCusto_Codigo IN (‘0100028’)

OR Hon.TBCentroCusto_Codigo IN (‘0100008’)

OR Hon.TBCentroCusto_Codigo IN (‘0100020’)

OR Hon.TBCentroCusto_Codigo IN (‘0100053’)

OR Hon.TBCentroCusto_Codigo IN (‘0100054’)

OR Hon.TBCentroCusto_Codigo IN (‘0100055’))

AND (Dad.TBDadosFatura_DataEntrada >= ‘20081101’

AND Dad.TBDadosFatura_DataEntrada <= ‘20081130’)

ORDER BY Dad.TBDadosFatura_Numero ASC, Dad.TBDadosFatura_DataEntrada ASC

Pois é galera,

 

Ou ninguém acessa meu blog ou ninguém arriscou a dizer
porque o SQL não utiliza meu índice. 😦

 

Vou dar mais alguns dias a vocês, antes de revelar “O
Segredo”

 

Quiz
– Porque o SQL não utiliza meu indice?

No nosso ambiente de desenvolvimento estamos utilizando o SQL Server 2008.

Peguei um script que estava dando erro em nosso cliente, fui testar, aqui funciona, lá não funciona.

Resumo, no SQL Server 2008 são permitidos inserts utilizando subqueries, coisa que não era permitida no SQL Server 2005.

Por ex:

A consulta abaixo não funciona no SQL Server 2005

DECLARE @Tab TABLE(ID Int)

INSERT INTO @Tab(ID) VALUES((SELECT 1))

Msg 1046, Level 15, State 1, Line 3

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Já no SQL Server 2008:

DECLARE @Tab TABLE(ID Int)

INSERT INTO @Tab(ID) VALUES((SELECT 1))

(1 row(s) affected)

Portanto fique alerta, para as construções dos seus inserts…