Funções Escalares – Resusabilidade, Formatação, Preguiça vs Desempenho

Bom Dia Pessoal,

Já faz algum tempo que queria escrever sobre funções escalares e recentemente com algumas threads no MSDN, me senti ainda mais motivado a escrever algumas palavrinhas a respeito. Vejo muita gente falando, usando e recomendando mesmo sem saber exatamente as penalidades envolvidas no uso de funções escalares.

O que são funções escalares ?

Função escalares são funções que recebem um ou mais valores retornam um valor. São exemplos de funções escalares naturais LTRIM, LEFT, CAST. A partir do SQL Server 2000 é possível a construção de funções escalares customizadas (User Defined Scalar Functions) obedecendo a mesma definição, ou seja, devem receber um conjunto de valores e retornar um valor. O uso das funções escalares está em consonância com um dos principais conceitos básicos da orientação, o encapsulamento. Uma vez que seja possível codificar uma lógica dentro da função (cálculo da taxa de juros, números de pedidos por empregado, ou ainda a formatação de uma data) é possível reaproveitá-la em vários trechos do códigos. O fato da função residir dentro do banco de dados também pode evitar a troca de contexto entre banco e aplicação.

Funções escalares e desempenho

Quando funções escalares customizadas são utilizadas em queries tendo como parâmetros de entradas valores das colunas referenciadas na instrução SELECT, normalmente há um alto custo de desempenho associado, pois, essas funções tem de ser chamadas uma vez para cada linha retornada (Row based) já que esse tipo de chamada não é otimizado pelo otimizador de consulta, pois, necessita de várias checagens adicionais (permissão, análise sintática, análise semântica, etc). Se uma função escalar necessita ser utilizada para conjuntos com poucos linhas, esse custo é imperceptível, mas quando grandes quantidade de linhas são necessárias, o custo da utilização das funções escalares torna-se cada mais visível.

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

— Cria uma tabela com 1 milhão de números
CREATE TABLE tNums (Num INT NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
    INSERT INTO tNums VALUES (@i)
    SET @i = @i + 1
END

— Marca a coluna Num como chave primária
ALTER TABLE tNums ADD CONSTRAINT PK_Num PRIMARY KEY (Num)

— Cria uma função que recebe um número e mostra o mesmo número mais um
CREATE FUNCTION fnAumenta1 (@Num INT)
RETURNS INT
As
BEGIN
    RETURN(@Num + 1)
END

— Retorna os 10 primeiros números com uso da função
SELECT Num, dbo.fnAumenta1(Num) FROM tNums
WHERE Num <= 10

— Retorna os 10 primeiros números seu o uso da função
SELECT Num, Num + 1 FROM tNums
WHERE Num <= 10

Se compararmos o plano de execução, as consultas são idênticas.

Consulta com função vs sem função

Entretanto, o Profiler nos revela algumas diferenças:

Trace (Consultas e Funções)

A execução do primeiro comando mostra que a função fnAumenta1 foi chamada 10 vezes (uma para cada linha do SELECT) e que a execução total teve de efetuar 7 leituras. A execução do segundo comando foi mais direta e resultou em apenas 3 leituras. Como a quantidade linhas envolvidas é bem pequena (apenas 10 registros), a recuperação dos dados foi praticamente instântanea em ambos os casos. A tabela abaixo mostra a evolução à medida que a quantidade de registros aumenta. Para essa tabela foi considerada apenas a execução do comando discartando-se os resultados (Tools, Options, Query Results, Results to Grid, Discard Results After Execution)

Função ? Registros CPU Leitura Tempo(ms)
Sim 100 0 5 3
Não 100 0 3 0
Sim 1.000 16 4 6
Não 1.000 0 4 2
Sim 10.000 31 20 53
Não 10.000 0 20 5
Sim 100.000 375 164 449
Não 100.000 31 164 31
Sim 1.000.000 4102 1615 4491
Não 1.000.000 219 1615 259

As primeiras diferenças começam a aparecer acima de 1.000 registros quando o uso da função começa a gastar ciclos de CPU mais significativos (em torno de 16).  Para a quantidade 10.000 registros percebe-se que a duração do comando com a função foi 10 vezes maior (53 /5). Para os volumes de 100.000 registros e 1.000.000 registros o processamento CPU com a solução baseada em function fica entre 10 a 20 vezes maior. O tempo de execução do comando também é aumentado nessa proporção.

Alguns irão argumentar que a diferença é imperceptível já que uma consulta normalmente retorna poucos registros. Sim, é verdade que para poucos registros, a diferença é mínima, mas é preciso lembrar que o uso da função não se aplica somente ao resultado da consulta. Se funções são utilizadas em tabelas com muitos registros, joins, etc, elas irão degradar o desempenho mesmo que o resultado final seja apenas algumas poucas linhas.

Funções Escalares e Formatação

Sempre defendi que a formatação dos dados para exibição não deve ficar no banco de dados. Qualquer aplicação corretamente modulada jamais deixaria as tarefas de formatação no banco de dados já que a camada de persistência tem como objetivo gravar e recuperar dados e a formatação fica para uma camada de exibição (ou no máximo de aplicação) e não em uma camada de persistência. Ainda assim, não é difícil encontrar consultas que formatam dados. Vejamos como o uso de funções escalares possibilita essa tarefa.

— Cria uma coluna de data
ALTER TABLE tNums ADD Data DATE NULL

— Atualiza a coluna de datas
UPDATE tNums SET Data = DATEADD(D,Num % 20,‘20110522’)

— Cria uma função para formatar a data
CREATE FUNCTION dbo.fnFormataData (@Data DATE)
RETURNS VARCHAR(30)
As
BEGIN
    DECLARE @DataRet VARCHAR(30)
    SET @DataRet = CONVERT(CHAR(10),@Data,103)
    SET @DataRet = @DataRet + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN ‘Terça-Feira’
            WHEN 3 THEN ‘Quarta-Feira’
            WHEN 4 THEN ‘Quinta-Feira’
            WHEN 5 THEN ‘Sexta-Feira’
            WHEN 6 THEN ‘Sábado’
            WHEN 7 THEN ‘Domingo’
        END + ‘ )’
    RETURN (@DataRet)
END

A função dbo.fnFormataData converte uma data para o formato 103 (dd/mm/yyyy) e em seguida adiciona um sufixo com o dia da semana. Vejamos o desempenho com o uso de uma consulta sem formatação, com formatação na consulta e com o uso da função de formatação:

— Retorna as datas distintamente
SELECT DISTINCT Data FROM tNums

— Retorna as datas distintamente com formatação
SELECT DISTINCT
        CONVERT(CHAR(10),Data,103) + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN
‘Terça-Feira’
            WHEN 3 THEN
‘Quarta-Feira’
            WHEN 4 THEN
‘Quinta-Feira’
            WHEN 5 THEN
‘Sexta-Feira’
            WHEN 6 THEN
‘Sábado’
            WHEN 7 THEN
‘Domingo’
        END + ‘ )’ FROM tNums

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

Trace (Formatação)

Como podemos ver, só a consulta pura gastou 200ms. Se colocarmos a formatação na consulta, esse tempo vai para 809ms. Ao meu ver é um enorme desperdício formatar dados na camada de banco. Veja que a consulta formatada teve 609ms (809 – 200) só para formatar dados, ou seja, do seu tempo total de execução, 25% do tempo foi para recuperar os dados e 75% do tempo para formatá-los. O uso da CPU para essa formatação foi quase 5 vezes maior (e olha que ainda fizemos um SCAN em uma tabela com 1.000.000 de linhas que representam apenas 3256 blocos nesse exemplo). Se os dados fossem devolvidos para que a aplicação formatasse, teríamos gastado menos recursos do SGBD e mais recursos do servidor de aplicação. Não há como “sumir” com o processamento, mas convenhamos que é mais correto deixar a formatação onde ela deve realmente ficar e vale a pena lembrar que servidores de aplicação escalam mais facilmente que um servidor de banco de dados, pois, dispõe de facilidades como NLB, Web Farm, Web Garden, etc.

A última consulta que usa a função escalar para formatar nem merece muita apreciação. Gastou mais de 20 vezes o total de CPU e quase 50 vezes mais lenta que a consulta de dados sem formatação. Notoriamente um grande desperdício. É comum reproduzí-lo quando se usa o DISTINCT em conjunto com formatação. Se for realmente imprescindível o uso da formatação, opte por fazê-la após o DISTINCT e não antes.

— Efetua o DISTINCT antes, formata depois
;WITH DadosDistintos As (
    SELECT DISTINCT Data FROM tNums)
SELECT DISTINCT dbo.fnFormataData(Data) FROM DadosDistintos

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

 Trace, DISTINCT e Formatação de Dados

Nesse caso, mesmo a UDF que era o pior dos casos teve seu desempenho muito reduzido tanto em termos de CPU quanto de duração do comando (algo em torno de 30 vezes mais rápido). Quando se usa DISTINCT, filtrar antes e formatar depois é uma excelente prática para ganhar desempenho (embora pouco conhecida e pouco utilizada).

Funções Escalares e Pregüiça

Alguns optam pelo uso de funções escalares para “escapar” de fazer um JOIN ou ainda “fugir” de um GROUP BY. Acredito que essa seja uma das piores utilizações para funções escalares.

— Cria uma função para contar quantas ocorrências existem por data
CREATE FUNCTION dbo.fnContaOcorrenciasPorData (@Data DATE)
RETURNS INT
As
BEGIN
    DECLARE @Total INT
    SET @Total = (
        SELECT COUNT(*) FROM tNums
        WHERE Data = @Data)
    RETURN(@Total)
END

— Cria uma tabela de eventos
CREATE TABLE Eventos (
    Data DATE,
    TipoEvento VARCHAR(50))
INSERT INTO Eventos VALUES (‘20110522’,‘Palestra Estagiários’)
INSERT INTO Eventos VALUES (‘20110523’,‘Café de Boas Vindas’)
INSERT INTO Eventos VALUES (‘20110524’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110525’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110526’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110527’,‘Visita à Matriz’)
INSERT INTO Eventos VALUES (‘20110528’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110529’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110530’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110531’,‘Encerramento do Mês’)

— Usa a função para contar quantas ocorrências existem para cada evento
SELECT TipoEvento, dbo.fnContaOcorrenciasPorData(Data) FROM Eventos

— Faz a mesma consulta com um JOIN e um GROUP BY
SELECT TipoEvento, COUNT(Num) FROM Eventos
INNER JOIN tNums ON Eventos.Data = tNums.Data
GROUP BY TipoEvento

Trace, Joins e GROUP BY

E novamente as funções escalares perdem no quesito desempenho. O uso da função gastou quase cinco vezes mais ciclos de CPU e a duração do comando foi quase dez vezes maior. O detalhe mais interessante está na coluna Reads. A tabela tNums possui 3256 blocos e a tabela Eventos possui apenas dois blocos. O JOIN faz um SCAN em ambas e por isso o total de leituras na implementação baseada em JOIN e GROUP BY foi de 3258 blocos. Já a função, como precisa fazer a contagem para cada linha, ela leu várias vezes o mesmo bloco fazendo com que a quantidade de leituras fosse dez vezes maior. Esse é o custo da “pregüiça”.

Mantendo a reusabilidade

Se apenas o desempenho for levado em consideração, provavelmente a recomendação geral seria não utilizar funções escalares e deixar a lógica na própria consulta ou ainda na aplicação. Entretanto, as funções escalares provêm o benefício da reusabilidade, pois, uma vez criadas, podem ser reaproveitadas em várias consultas sem a necessidade de remotar sua lógica em cada consulta. É visível que isso custa desempenho, mas é possível manter o benefício da reusabilidade reduzindo essa penalidade. Vejamos como fazê-lo.

— Cria uma tabela de cotações
CREATE TABLE Cotacoes (
    DataRef DATE NOT NULL,
    Taxa DECIMAL(7,4))

— Cadastra algumas taxas
INSERT INTO Cotacoes VALUES (‘20110501’,0.0142)
INSERT INTO Cotacoes VALUES (‘20110601’,0.0157)
INSERT INTO Cotacoes VALUES (‘20110701’,0.0167)
INSERT INTO Cotacoes VALUES (‘20110801’,0.0135)
INSERT INTO Cotacoes VALUES (‘20110901’,0.0103)
INSERT INTO Cotacoes VALUES (‘20111001’,0.0121)
INSERT INTO Cotacoes VALUES (‘20111101’,0.0158)
INSERT INTO Cotacoes VALUES (‘20111201’,0.0174)

— Monta um função de cálculo de montante final
CREATE FUNCTION dbo.FnRetornaRentabilidade (@DataInicio DATE, @Dias SMALLINT)
RETURNS DECIMAL(9,6)
As
BEGIN

— Inicializa a variável para calcular a taxa
DECLARE @Taxa DECIMAL(9,6)

— Calcula o mês de referência
DECLARE @DataRef DATE
SET @DataRef = (DATEADD(D,1-DAY(@DataInicio),@DataInicio))

— Calcula a taxa proporcional em relação ao início do mês de referência
DECLARE @DiasFimMes SMALLINT
SET @DiasFimMes = DATEDIFF(D,@DataInicio,DATEADD(M,1,@DataRef))

SET @Taxa = (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef)
SET @Taxa = POWER(1 + @Taxa,@DiasFimMes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4))) – 1

— Calcula o restante dos dias úteis
DECLARE @DiasRestantes SMALLINT
SET @DiasRestantes = @Dias – @DiasFimMes

— Aumenta a data de referência em um mês
SET @DataRef = DATEADD(M,1,@DataRef)

WHILE (@DiasRestantes > 0)
BEGIN
    — Verifica se irá fechar o próximo mês
    IF @DiasRestantes > DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
    BEGIN
        SET @Taxa = ((1 + @Taxa) * (1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef))) – 1
        SET @DiasRestantes = @DiasRestantes – DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
        SET @DataRef = DATEADD(M,1,@DataRef)
    END
    ELSE
    BEGIN
        SET @Taxa = (1 + @Taxa) * POWER(1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef),
        @DiasRestantes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4)))
        SET @DiasRestantes = 0
    END
END

— Se for menos de 40 dias, não há rendimento
SET @Taxa = CASE WHEN @Dias < 40 THEN 0 ELSE @Taxa – 1 END

RETURN(@Taxa)

END

— Testa a função
SELECT dbo.FnRetornaRentabilidade(‘20110511’,40)

De acordo com as taxas oferecidas, se um investimento for feito 11/05/2011 e durar 40 dias, ele irá render 1,9592% que correspondem a 20 dias na rentabilidade de maio 1,42% e 20 dias na rentabilidade junho (1,57%). Não vou entrar nos detalhes desse cálculo (isso fica para uma outra oportunidade), mas ao contrário das abordagens anteriores, esse é o tipo de lógica que, embora possível de ser incorporada a uma consulta diretamente, normalmente ficará dentro de uma função escalar, pois, será bem oportuno que esse cálculo possa ser encapsulado e reutilizado. Será que é possível amenizar a perda de desempenho provocada pelo uso da função linha a linha ?

— Cria uma tabela de aplicações financeiras
CREATE TABLE AppFin (
    ClienteID INT NOT NULL,
    DataApp DATE NOT NULL,
    Valor MONEY NOT NULL,
    DiasCorridos SMALLINT NOT NULL)

— Restringe o investimento a no mínimo 40 dias
ALTER TABLE AppFin ADD CONSTRAINT CK_DiasCorridos CHECK (DiasCorridos >= 40)

— Insere alguns investimentos de forma aleatória
DECLARE @qtdInvestimentos INT
DECLARE @MaxClienteID INT
DECLARE @MaxDataApp TINYINT
DECLARE @MaxValor MONEY
DECLARE @MaxDiasCorridos TINYINT

SET @qtdInvestimentos = 500000
SET @MaxClienteID = 1000
SET @MaxDataApp = 5
SET @MaxValor = 1000000
SET @MaxDiasCorridos = 2

DECLARE @i INT
SET @i = 1

WHILE @i <= @qtdInvestimentos
BEGIN
    INSERT INTO
AppFin VALUES (
        ABS(CHECKSUM(NEWID())) % @MaxClienteID,
        DATEADD(M,ABS(CHECKSUM(NEWID())) % 5,‘20110511’),
        ABS(CHECKSUM(NEWID())) % @MaxValor / 100.0000,
        ((ABS(CHECKSUM(NEWID())) % @MaxDiasCorridos + 1) * 30) + 10)
    SET @i = @i + 1
END

— Efetua a consulta para retornar a rentabilidade
SELECT
    ClienteID, DataApp, Valor, DiasCorridos,
    dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
FROM AppFin

Trace, UDF e Reusabilidade

Considerando que a tabela AppFin possui 1697 páginas, os resultados do cálculo da do foram expressivos. Para realizar a consulta em questão foram necessárias 3752278 páginas. Isso significa mais de 2.200 vezes o total de páginas da tabela além de um gasto de CPU e tempo muito significativos (quase 30 segundos). Será que há otimização possível ?

No exemplo, a rentabilidade é influenciada basicamente pelos parâmetros DataApp e DiasCorridos dispensado as demais colunas da tabela AppFin. Possivelmente haverá várias repetições dessa combinação. Vejamos quantas combinações distintas existem nessas duas colunas.

— Verifica quantas combinações distintas existem para o cálculo da rentabilidade
SELECT DISTINCT DataApp, DiasCorridos FROM AppFin

No meu exemplo foram retornadas apenas 10 ocorrências. Penso que calcular a rentabilidade para 10 ocorrências é possivelmente mais eficiente que calcular a rentabilidade para 500.000 ocorrências principalmente porque das 500.000 temos apenas dez tipos. Minha proposta seria efetuar o cálculo separadamente para essas dez e posteriormente efetuar um JOIN com as outras 500.000. Vejamos se essa abordagem funciona.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN RentabilidadeCalculada As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Trace, UDF e Reusabilidade

Embora as CTEs tenham me ajudado no exemplo da formatação, dessa vez elas me deixaram na mão. Os indicadores (CPU, tempo e leituras) são muito próximos com uma leve desvantagens para as CTEs. Ainda assim, a idéia de fazer 10 vezes e não 500.000 ainda me parece mais inteligente. Vejamos agora o resultado com uma tabela temporária.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
— Utiliza uma TMP para persistir os resultados temporariamente
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT DataApp, DiasCorridos, Rentabilidade INTO #TMP FROM RentabilidadeCalculada

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN #TMP As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Trace, UDF e Reusabilidade

Dessa vez os resultados foram bastante significativos, além de reduzir as consultas anteriores de 27 segundos para pouco mais de meio segundo, tivemos um ganho expressivo em termos de CPU (menos de 3% do consumo anterior) e uma economia de leituras na casa de 99% (3602 conta 3752276). Definitivamente, fico com essa implementação. Ela mantem a resusabilidade sem comprometer o desempenho. A lógica é simples, se a função precisa ser feita linha a linha, é melhor fazer o menor número de vezes possível.

Confesso que ainda não descobri o porquê das CTEs terem falhado nesse caso. Normalmente são alternativas mais interessantes que o uso de tabelas temporárias, mas enfim… Isso é assunto para uma nova pesquisa e aprendizado.

O uso de funções escalares customizadas não é ruim no SQL Server. Pode encapsular lógicas complexas, facilitar a manutenção do código, prover mecanismos de segurança entre várias outras utilidades. Entretanto, uma boa implementação não consiste no seu uso indiscriminado, mas sim na sua aplicação de forma planejada conhecendo suas vantagens, as penalidades associadas e a forma de lidar com elas.

[ ]s,

Gustavo

9 Respostas para “Funções Escalares – Resusabilidade, Formatação, Preguiça vs Desempenho

  1. Oi Gustavo bom dia… parabéns pelo post, mto bem explicado.

    Tenho uma dúvida que talvez fuja um pouco do seu post, mas é relacionado… No Oracle é possível eu criar um “pacote” (package) para agrupar funções, como eu crio um “pacote” no SQL Server 2005 para agrupar várias funções escalares?

    *No link abaixo tem um exemplo de pacotes em Oracle, do modo pelo qual me referi:
    http://imasters.com.br/artigo/909/oracle/package_-_parte_1/

    • Bom Dia Capitão,

      Infelimente o SQL Server não tem um recurso equivalente ao Package do Oracle. O máximo que conseguiríamos é agrupar as funções no mesmo esquema ou ainda adotar um padrão de nomenclatura que possibite isso, mas nenhuma das duas soluções representa o Package do ORACLE.

      [ ]s,

      Gustavo

  2. Meu caro na verdade é uma pergunta, estou necessitando fazer um controle de estoque em um banco q estou desenvolvendo, lhe pergunto se vc tem algum material de me ajuda,

    Obs.: Sou parter e uso a versão 2008R2, grato

    • Boa Noite Ribamra,

      Infelizmente não possuo nenhum material que possa indicar para essa finalidade. Acredito que você encontrará em algum livro de programação ou de modelagem, mas não tenho nenhum em mente.

  3. Alexandre Eduardo

    Ótimo post Gustavo. Percebi essas alterações no desempenho utilizando funções e formatações, depois de ter assistido uma de suas aulas falando sobre como o SQL realiza essas consultas(se eu não me engano foi no curso 2778)… depois disso fui me divertir testando as query’s e ficando atento ao desempenho das mesmas…

    Passei a estudar soluções para deixar as rotinas mais otimizadas, como por exemplo, deixar que a aplicação faça a formatação ou execute a função… Mas tem um porém, se a aplicação for desktop ou WEB? Deixo esse tipo de processamento por parte do servidor WEB(Sistemas WEB), na máquina cliente(Sistemas Desktop) ou no servidor de banco? A escolha dependeria dos recursos disponivels para o servidor web quanto para o servidor de banco?

    Fiquei intrigado quanto a performance das CTE’s perante as tabelas temporárias… achei que o mesmo seria mais rápido que elas… agora estou curioso em saber o seu funcionamento mais a fundo… XP

    Abraçooo!!!

    • Oi Alexandre,

      Deve ter sido no 2778 (eu sempre friso isso hehehehe). Que bom que os ensinamentos estão sendo aplicados.
      Eu indico que a formatação fique fora do servidor de banco. Se sua aplicação for Web, deixe no servidor Web. Se sua aplicação for Desktop, deixe no servidor de componentes (rs). Senão houver servidor de componentes então há um dilema. No desktop, você vai ter que redistribuir quando mudar a formatação (isso custa). No servidor de banco de dados você já sabe… Aí é avaliar (nesse caso o banco pode ser mais em conta, pois, reinstalar em todas as máquinas…)

      Eu também fiquei intrigado, mas o Rodrigo nos colocou uma referência.

      [ ]s,

      Gustavo

  4. Muito bom esse post Gusttavo… difícil achar bons artigos em português…
    Sobre a parte lá das CTEs, eu também já levei alguns tombos delas…
    Achei que tabelas temporárias eram inúteis quando descobri as cTEs… mas nada melhor na vida do que quebrar a cara e ver, que se aquilo existe, é porque é server pra alguma coisa…

    Eu li esse post [http://blogs.msdn.com/b/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx] do Craig e confirmei minhas suspeitas…

    A CTE só é um forma de economizar código … rs.
    Eu posso tá sendo grosseiro, mas o fato é que: é como se o sql pegasse aquele trecho inteiro e repetisse, ou seja, ele vai gerar um plano, como se voce estivesse escrito o código que tá dentro da CTE, lá no join.

    Eu já consegui simular alguns casos em que planos diferentes são gerados quando usa CTE e quando usa o código direto no lugar da CTE…mas raro…

    O otimizador deve ter considerado todas as linhas, antes de fazer o DISTINCT da CTE …

    No caso da tabela temporária, a gente “materializou” a CTE, como disse Craig, por isso deu resultados melhores, e o SQL considerou somente as 10 linhas na hora de otimizar…

    Bom, é isso o que eu acho, nao sei se já tem outro artigo sobre isso, se tiver vou achar…

    Mas muito bom mesmo… Acho que o SQL Server deveria ter algo com as macros do C … incorpora o código antes de compilar, mas podemos reusar em várias partes …

    []’s
    Rodrigo
    Microsoft MTA

  5. Pingback: T-SQL Função Split | andersonoliveirasilva

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s