Funcões escalares no SQL Server com valores nulos – Uma possível bala de prata

Tempo de leitura: 5 minutos

Hoje gostaria de dar uma dica rápida sobre de como conseguir performance com funções escalares no SQL Server, todos sabemos que esse tipo de objeto é “assassino” de desempenho por muitas razões conhecidas (que nao serão abordadas hoje nesse artigo), no entanto, existem cenários que você não pode alterar as funções, pode ser que ela seja parte de uma aplicação de terceiro e coisas do tipo.

Esse foi um caso de suporte que um cliente abriu e nos informou que comando SELECT custava X% de CPU para algumas chamadas e, um valor diferente para outras, sendo assim, ele gostaria de entender a causa raiz desse comportamento, pois, na visão dele o SQL Server estava se “perdendo”.

Sendo assim, vamos ao nosso exemplo, estou utilizando o banco de dados Northwind junto com a tabela CustomerBigs que faz parte de um dos scripts do Fabiano Amorim.

Esse teste foi realizada na seguinte versão do SQL Server:

Para essa DEMO, eu criei duas funções escalares que adicionam “X” para complementar uma string, coisa simples apenas para ter algo e disparar dentro da funnção, de acordo com script

USE Northwind
go
CREATE FUNCTION [dbo].[AdicionaX](
      @Value varchar(10)
  ) 
  RETURNS varchar(10)
  AS 
  BEGIN
 	DECLARE @Retorno varchar(8);
      SET @Retorno = CONVERT(varchar(8), @Value);
      SET @Retorno = REPLICATE('X', 8 - DATALENGTH(@Retorno)) + @Retorno;
      RETURN (@Retorno);
  END;
  GO


  go
CREATE FUNCTION [dbo].[AdicionaX_TC](
      @Value  varchar(10)
  ) 
  RETURNS varchar(8)
  WITH RETURNS NULL ON NULL INPUT  
  AS 
  BEGIN
      DECLARE @Retorno varchar(8);
      SET @Retorno = CONVERT(varchar(8), @Value);
      SET @Retorno = REPLICATE('X', 8 - DATALENGTH(@Retorno)) + @Retorno;
      RETURN (@Retorno);
  END;
  GO

USE Northwind

CREATE FUNCTION [dbo].[AdicionaX](

@Value varchar(10)

)

RETURNS varchar(10)

BEGIN

DECLARE @Retorno varchar(8);

SET @Retorno = CONVERT(varchar(8), @Value);

SET @Retorno = REPLICATE('X', 8 - DATALENGTH(@Retorno)) + @Retorno;

RETURN (@Retorno);

END;

CREATE FUNCTION [dbo].[AdicionaX_TC](

@Value varchar(10)

)

RETURNS varchar(8)

WITH RETURNS NULL ON NULL INPUT

BEGIN

DECLARE @Retorno varchar(8);

SET @Retorno = CONVERT(varchar(8), @Value);

SET @Retorno = REPLICATE('X', 8 - DATALENGTH(@Retorno)) + @Retorno;

RETURN (@Retorno);

END;

Notem que, a única diferença de uma função para outra é a opção “RETURNS NULL ON NULL INPUT”. Se você olhar a documentação oficial, vejam o que ela diz sobre essa opção:

“Specifies the OnNULLCall attribute of a scalar function. If not specified, CALLED ON NULL INPUT is implied by default. This means that the function body executes even if NULL is passed as an argument.”

Se você não especificar o default seria a opção “called on null input” que significa que o corpo da função escalar vai ser executado até pra valores nulos? Espera, que dizer que se especificar essa opção minha função não vai ser executado quando o valor de entrada for nulo? BASICAMENTE SIM. O que nos remete a outro questionamento, vou ter ganho de desempenho para 100% dos cenários? A resposta é não, pois, se o valor da entrada for um “non-null value” a função ainda será executada. Digamos que essa solução seria uma “meia bala de prata” que não cabe para todos os cenários.

Vamos criar um seguinte ExEvent para filtrar pela minha sessão, nesse extended event eu adicionei dois eventos: module_end e sql_batch_completed. O module_end é apenas para gente ver a função sendo chamada para cada linha retornada (outro problema de função escalar). Para esse cenário, irei adicionar a opção track_causality = on pra saber quantas vezes a função foi executada, preste atenção que eu estou filtrando pela minha sessão(66).

 CREATE EVENT SESSION [TCTest] ON SERVER 
 ADD EVENT sqlserver.module_end(
      WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(66)))),
 ADD EVENT sqlserver.sql_batch_completed(
      WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(66)))),
 ADD EVENT sqlserver.sql_statement_completed(
      WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(66))))
 WITH (TRACK_CAUSALITY=ON)
 GO

CREATE EVENT SESSION [TCTest] ON SERVER

ADD EVENT sqlserver.module_end(

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(66)))),

ADD EVENT sqlserver.sql_batch_completed(

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(66)))),

ADD EVENT sqlserver.sql_statement_completed(

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(66))))

WITH (TRACK_CAUSALITY=ON)

Execute a instrução SELECT abaixo que chama a função “AdicionaX” e veja que a quantidade de linhas retornada é de 29080 registros.

--Primeiro Test
SELECT 
	[CustomerID], [CityID], [CompanyName], [ContactName], [Col1], [Col2], [CNPJ]
	,dbo.[AdicionaX](CityID) 
FROM [dbo].[CustomersBig]
WHERE CustomerID > 290919 and CustomerID < 320000
GO

--Primeiro Test

SELECT

[CustomerID], [CityID], [CompanyName], [ContactName], [Col1], [Col2], [CNPJ]

,dbo.[AdicionaX](CityID)

FROM [dbo].[CustomersBig]

WHERE CustomerID > 290919 and CustomerID < 320000

Veja o extended event e perceba que a função executou para todas as linhas sendo retornadas (attach_activity_id_seq) e também vamos tomar nota do custo de CPU e duration para esse simples SELECT.

Agoramos vamos executar o nosso select, agora passando a segunda versão da nossa função AdicionaX_TC que contém a opção “RETURNS NULL ON NULL INPUT”. A quantidade de linhas é a mesma, como podemos ver no print.

 SELECT 
	[CustomerID], [CityID], [CompanyName], [ContactName], [Col1], [Col2], [CNPJ]
	,dbo.[AdicionaX_TC](CityID) 
  FROM [dbo].[CustomersBig]
  WHERE CustomerID > 290919 and CustomerID < 320000

SELECT

[CustomerID], [CityID], [CompanyName], [ContactName], [Col1], [Col2], [CNPJ]

,dbo.[AdicionaX_TC](CityID)

FROM [dbo].[CustomersBig]

WHERE CustomerID > 290919 and CustomerID < 320000

Mas, se olharmos para o resultado do ExEvent dessa vez, vamos ver que a função não foi executada para todas as linhas e apenas para as que possuem o valor não nulo (19999) com um ganho médio de 27% de melhoria para o duration e 14% de CPU time.

Vamos fazer um select na tabela, filtrando pelo customer id que usamos na consulta e contar quantos valores nulos e não nulos nós temos.

 SELECT SUM(CASE WHEN CityID IS NOT NULL THEN 1 ELSE 0 END) AS ValoresNaoNulos,
                SUM(CASE WHEN CityID IS NULL THEN 1 ELSE 0 END) AS ValoresNulos,
                COUNT(*) as TotalLinhas
  FROM [dbo].[CustomersBig]
  WHERE CustomerID > 290919 and CustomerID < 320000

SELECT SUM(CASE WHEN CityID IS NOT NULL THEN 1 ELSE 0 END) AS ValoresNaoNulos,

SUM(CASE WHEN CityID IS NULL THEN 1 ELSE 0 END) AS ValoresNulos,

COUNT(*) as TotalLinhas

FROM [dbo].[CustomersBig]

WHERE CustomerID > 290919 and CustomerID < 320000

Agora se fizermos um select, onde apenas valores não nulos serão retornados para ambas as funções, não teremos ganho de desempenho, pois, a função vai ser executada para todas as linhas, conforme a seguir:

--So retorna valores nao nulos, vejamos que a função é executado para todas as linhas
SELECT 
	[CustomerID], [CityID], [CompanyName], [ContactName], [Col1], [Col2], [CNPJ]
	,dbo.[AdicionaX_TC](CityID) 
  FROM [dbo].[CustomersBig]
  WHERE [CustomerID] >= 300001 and CustomerID <=329000
  GO

--So retorna valores nao nulos, vejamos que a função é executado para todas as linhas

SELECT

[CustomerID], [CityID], [CompanyName], [ContactName], [Col1], [Col2], [CNPJ]

,dbo.[AdicionaX_TC](CityID)

FROM [dbo].[CustomersBig]

WHERE [CustomerID] >= 300001 and CustomerID <=329000

Nesse exemplo apenas usei a segunda versão da função, pois, a primeira já sabemos que vai executar pra todas as linhas como já vimos e veja o resutlado no exEvent:

A função foi chamada para todos os valores, sendo assim, não existe a “bala de prata” quando se fala de funções escalares, mas, existem alguns pontos que podemos melhorar e vale ficar mencionado que isso pode não funcionar 100% das vezes , vai depender das linhas que são retornadas.

Espero que tenham gostado!!!!

Abs

Funcões escalares no SQL Server com valores nulos – Uma possível bala de prata