Scribnasium

br>Talvez tenha ouvido dizer que as funções definidas pelo utilizador do SQL Server são o diabo. Isso é porque alguém se queimou. Colocaram uma função escalar numa cláusula WHERE numa grande mesa e deram-lhe um nome de coluna. E coisas más aconteceram.

Neste caso, “más” significa lentas. Realmente lento.

Sarga-Que-Agora

Para que as consultas tenham um bom desempenho, o SQL Server precisa de ser capaz de encontrar os dados rapidamente, e isso geralmente significa utilizar um índice. Um índice é uma lista de dados chave de uma tabela, organizada para tornar a procura mais rápida. Tal como num livro, em vez de digitalizar cada página para uma menção de uma palavra, pode utilizar o índice, onde o autor – ou o seu programa de computador – fez esse trabalho para si como uma referência útil.

Um índice é mais rápido quando o pode utilizar directamente. Se procura páginas que mencionam “SQL”, encontra “SQL” no índice e este diz-lhe os números das páginas a verificar. Mas e se estiver à procura de todas as palavras que terminam em “SQL”, como “T-SQL”? Agora não pode ir directamente para a parte do índice que deseja. Tem de digitalizar todo o índice. Já não tem um único argumento de pesquisa (“SQL”); tem uma expressão de pesquisa (“termina em SQL”). O termo industrial que usamos é que a sua pesquisa já não é “sargable” (“Search ARGument ABLE”).

A moral da história é que se quiser um índice utilizado da forma mais eficaz, certifique-se de que está a pesquisar exactamente o que está a ser indexado. Se estiver a procurar por uma data, e quiser encontrar tudo num determinado ano, não use WHERE Year() = 2018". Em vez disso, use WHERE >= '20180101' AND . As a human, you can understand these as the same thing, but often the optimizer just sees "arbitrary function on indexed column" and figures it can't use the index the normal way but instead has to slog through the whole thing and run the function to see what it does.

E se o optimizador tiver este problema com a função integrada “Ano”, o que acha que vai fazer com o seu escalar “Complex_2000_Line_Monstrosity”? Vai tratá-lo como a caixa negra que é e percorrer todas as entradas do índice para o testar. E isso pressupõe a existência de um índice. Uma grande função definida pelo utilizador necessita frequentemente de dados de várias colunas diferentes, algumas das quais não se encontram num índice. Por isso, agora está a percorrer toda a tabela. Agarre-se a uma taça de joe. Estará lá durante algum tempo.

Pode ver através deles.

Embora as funções escalares sejam opacas para o optimizador, as funções em linha são transparentes. Em vez de ver uma função em linha como uma caixa negra, o optimizador de consultas vê-a como uma visão parametrizada – ou seja, uma visão que por acaso toma parâmetros. As vistas e funções inline são essencialmente copiadas e coladas na consulta que as chama, como se não fossem de todo uma construção separada.

Sim>>Sim>/td>

Agora só porque os limites da sua função se desvanecem na terra do optimizador não significa que encontrará a sua procura. Não precisa de uma função para destruir a saragabilidade. Basta rearranjar a sua lógica de critérios para lidar com isso por si próprio. Por exemplo, não obterá o benefício do seu índice em Column1 se usar WHERE 1 = CASE WHEN Column1 = @value THEN 1 ELSE 0 END em vez de WHERE Column1 = @value. Mais uma vez, nós humanos podemos ver que é a mesma coisa, mas o SQL Server não.

mas se utilizar uma função inline que actua directamente num campo indexado, sem um cálculo ofuscante, o optimizador continuará a utilizar uma procura indexada. A forma de o fazer acontecer parece-se um pouco mais com arte do que com ciência.

Pudding

Consider an Orders table with an OrderID column, an OrderStatusID column, and an OrderDate column. Os OrderID e OrderDate são irrelevantes para os nossos exemplos, excepto para fornecer alguns dados adicionais relacionados com encomendas. A coluna chave aqui é OrderStatusID. Digamos que temos muitos estados em que uma encomenda poderia estar (Novo, Processamento, Envio, Pago, etc.).

Agora digamos que temos uma regra de negócio que se aplica apenas em alguns estados. Talvez uma encomenda seja tratada por um determinado departamento apenas se for Nova ou Processamento, mas não quando em qualquer outro estatuto. Se colocar esta lógica de negócio na base de dados – como deve – o seu código de base de dados terá de compreender quais os estados que são especiais.

A forma mais eficiente de consultar a tabela de encomendas para as encomendas de estado especial é listá-las directamente em cada consulta relevante:

SELECTo.*FROM#Orders oWHEREo.OrderStatusID IN (3,8);

Com um índice no campo OrderStatusID, o optimizador utilizaria provavelmente uma acção de Procura de Índice e encontraria as suas filas lambida-split. No entanto, provavelmente precisará da base de dados para compreender esse conceito em muitos locais diferentes. Em vez de repetir esta lista de “estados especiais”, decide que quer uma função para calcular se um estado é especial. Assim, a sua lógica empresarial é centralizada:

CREATE FUNCTION IsOrderStatusSpecialScalar(@OrderStatusID int)RETURNS bit ASBEGINRETURN(CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END);

Agora, sempre que quiser encontrar estes estados especiais, basta usar a função.

SELECTo.*FROM#Orders oWHEREdbo.IsOrderStatusSpecialScalar(o.OrderStatusID) = 1;

Apesar disso, encontra um scan de índice lento porque o optimizador não sabe o que se passa dentro da função. Simplesmente chama-o repetidamente para cada fila.

Pode pensar que pode recuperar o seu desempenho utilizando uma função em linha.

CREATE FUNCTION IsOrderStatusSpecial(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderStatusIsSpecial =CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END;

Terá de alterar um pouco o uso, mas é igualmente conciso.

SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecial(o.OrderStatusID) sWHEREs.OrderStatusIsSpecial = 1;

Felizmente, depara-se com o mesmo problema de desempenho de scan de índice. No entanto, não se pode realmente culpar a função em linha. Afinal, obtém o mesmo mau desempenho usando a função CASEWHEN lógica directamente:

SELECTo.*FROM#Orders oWHERE1 = CASEWHEN o.OrderStatusID IN (3,8) THEN1ELSE0END;

Por isso, é necessário encontrar uma forma de utilizar uma função em linha onde o optimizador veja uma utilização mais directa do campo indexado. Assim, cria-se uma função inline que não utiliza qualquer cálculo para produzir o seu bit.

CREATE FUNCTION IsOrderStatusSpecialLimiter(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderIsSpecial = CONVERT(bit,1)WHERE@OrderStatusID IN (3,8);

E nem sequer se utiliza o bit que ele faz quando se chama a função. Deixa que os resultados da função tenham impacto directo nas linhas devolvidas, via filtração.

SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecialLimiter(o.OrderStatusID) s;

e voilá! Tem o seu bolo de encapsulamento lógico e também pode comer o seu índice de procura de desempenho.

Mas essa não é a única forma de fazer isto funcionar. E é aqui que começa a sentir-se um pouco mais como uma arte. Em vez de ter a função inline a efectuar um cálculo ou mesmo linhas limite com uma lista WHERE cláusula com uma lista IN, poderia construir uma tabela virtual de estados especiais na sua função inline.

CREATE FUNCTION GetSpecialOrderStatusIDs()RETURNS TABLE ASRETURNSELECTs.OrderStatusIDFROM(SELECT3UNION ALLSELECT8) AS s(OrderStatusID);

Nem sequer está a passar nada para esta função. De facto, até se poderia usar uma simples vista antiga. E utilizá-la-ia de uma forma mais tradicional, sem aquele operador novato APPLY.

SELECTo.*FROM#Orders oWHEREEXISTS(SELECT*FROMGetSpecialOrderStatusIDs() sWHEREs.OrderStatusID = o.OrderStatusID);

Este é mais um exemplo de utilização de uma função em linha sem sacrificar o desempenho.

Hide and No Seek

So não, as funções definidas pelo utilizador não são o diabo. Funções escalares definidas pelo utilizador podem causar grandes problemas se forem mal utilizadas, mas geralmente funções definidas pelo utilizador em linha não causam problemas.

A verdadeira regra geral não é evitar funções, mas sim evitar adornar os seus campos de índice com lógica ou funções. Porque quando esconde as suas intenções do optimizador com sintaxe complexa, arrisca-se a não obter o melhor desempenho da procura de índice.