Una vez hemos discutido el rendimiento de las funciones escalares sin acceso a datos, llega el turno de ver qué ocurre cuando la función en cuestión accede a información alojada en objetos. Para este post vamos a volver a utilizar los 3 recursos que tenemos a nuestra disposición para programar nuestras funciones:

En el post anterior estábamos demostrando cómo obtener el mejor rendimiento de funciones sencillas sin acceso a datos, bastante comunes cuando uno está codificando la típica función de «dbo.ObtenerNombreCompleto(nombre,apellido1,apellido2)» y cosas así…pero en este caso nos vamos a centrar en funciones cuyo parámetro de entrada nos sirva como pivote sobre el que realizar algún acceso a datos y cálculo intermedio.  Este es el típo de funciones a evitar generalmente, pero aun así puede que necesitemos hacer uso de ellas o que sencillamente ya la tengamos hecha y no podamos deshacernos de ella tan fácilmente.

Codificación de UDFs

Para este post vamos a programar una función que haga un cálculo complejo y con dicho resultado realice una búsqueda a una tabla para devolver el resultado de una concatenación de texto como resultado. Veremos la aproximación de su código desde las tres opciones que tenemos con SQL Server 2016:

En esta ocasión también podríamos programar una UDFInline pensando un poco, pero la intención de este post es suponer que no podemos tomándonos la licencia de pensar que el código es mas complejo en la vida real

1) Función escalar T-SQL (UDF)

En este caso como decía he optado por una función que tiene cálculos «complejos de CPU» para evitar el efecto cambio de contexto del anterior post y además tras finalizar el cálculo se ha añadido un acceso a datos con la query hacia dbo.Random_Strings

CREATE FUNCTION [dbo].[DummyCalculationWithDataAccess]
(
  @num1 INT,
  @num2 int
)
RETURNS VARCHAR(200)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @steps INT = @num1 % 123456
  DECLARE @no_of_sides_in_the_polygon INT =@num2 % 54321
  DECLARE @tmp2 FLOAT = 1.0
  DECLARE @tmp FLOAT
    DECLARE @return VARCHAR(200)
   
  SET @tmp = SIN(pi()/180*(180.0 - 360/(@no_of_sides_in_the_polygon + @steps))/2)/sin(pi()*2/(@no_of_sides_in_the_polygon + @steps))
  IF(@tmp < 1.0)
    SET @tmp2 = 1.0
  IF (@tmp > 1.0 AND @tmp < 5000)
    SET @tmp2 = 5.0
  IF (@tmp >= 5000 AND @tmp < 100000)
    SET @tmp2 = 6.0
  IF(@tmp >=100000 AND @tmp < 200000)
    SET @tmp2 = 7.0
  IF (@tmp >=200000)
    SET @tmp2 = 9.0

  SET @tmp = @tmp * (1.0 -@tmp2)

  -- Indirect data access
  SELECT @return = String1 + String2
  FROM dbo.Random_Strings
  WHERE id = ABS(CAST(@tmp+1 AS INT)%100000 )

  RETURN (@return)
END

Un ejemplo de uso podría ser:

SELECT AVG(LEN((dbo.DummyCalculationWithDataAccess(n,n2))))
FROM dbo.Numbers_OnDisk

 

2) Funcion escalar CLR(UDFclr)

Obviamente hay que codificar la función en C# y desplegarla con su objeto SQLCLR (cosa que puedes hacer de forma sencilla con el proyecto SQLServerScalarUDFPerformance) :

[Microsoft.SqlServer.Server.SqlFunction(IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.Read)]
   public static SqlString DummyCalculationWithDataAccessClr(SqlInt32 num1,SqlInt32 num2)
   {
       double steps = (double)num1 % 123456;
       double no_of_sides_in_the_polygon = (double)num2 % 54321;
       double tmp2 = 1.0;
       double tmp;
       string retorno;

       tmp = Math.Sin(Math.PI / 180 * (180.0 - 360 / (no_of_sides_in_the_polygon + steps)) / 2) / Math.Sin(Math.PI * 2 / (no_of_sides_in_the_polygon + steps));

       if (tmp < 1.0)
           tmp = 1.0;
       if (tmp > 1.0 && tmp < 5000)
           tmp2 = 5.0;
       if (tmp >= 5000 && tmp < 100000)
           tmp2 = 6.0;
       if (tmp >= 100000 && tmp < 200000)
           tmp2 = 7.0;
       if (tmp >= 200000)
           tmp2 = 9.0;

       tmp = tmp * (1.0 - tmp2);

       // Now the DataAccess part
       using (SqlConnection conn = new SqlConnection("context connection=true"))
       {
           SqlCommand command = new SqlCommand();
           command.Connection = conn;
           conn.Open();

           int valor = Math.Abs((int)tmp+1 % 100000);
           string query = @"SELECT String1 + String2
                           FROM dbo.Random_Strings
                           WHERE id = @p1";
           command.Parameters.AddWithValue("@p1", valor);
           command.CommandText = query;
           command.CommandType = CommandType.Text;

           retorno = (string)command.ExecuteScalar();
           if (String.IsNullOrEmpty(retorno))
           {
               retorno = String.Empty;
           }

       }

       return (retorno);
   }
Nótese que hemos utilizado «context connection» y un comando parametrizado, siendo esta la forma óptima (también lo hubiera sido con un stored procedure).

Un ejemplo de uso podría ser:

SELECT AVG(LEN((dbo.DummyCalculationWithDataAccessClr(n,n2))))
FROM dbo.Numbers_OnDisk

 

3) Función escalar compilada nativa para In-Memory OLTP (UDFInMemory)

Este caso es prácticamente un copy-paste de la UDF del punto 1, pero indicando que es un objeto nativo y por tanto requiere acceso a un objeto in-memory donde tengamos los Random_Strings

CREATE FUNCTION inmemory.[DummyCalculationWithDataAccessInMemory]
(
  @num1 int,
  @num2 int
)
RETURNS VARCHAR(200)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (
  TRANSACTION ISOLATION LEVEL = SNAPSHOT,
  LANGUAGE = N'English')


  DECLARE @steps INT = @num1 % 123456
  DECLARE @no_of_sides_in_the_polygon INT =@num2 % 54321
  DECLARE @tmp2 FLOAT = 1.0
  DECLARE @tmp FLOAT
    DECLARE @return VARCHAR(200)
   
  SET @tmp = SIN(pi()/180*(180.0 - 360/(@no_of_sides_in_the_polygon + @steps))/2)/sin(pi()*2/(@no_of_sides_in_the_polygon + @steps))
  IF(@tmp < 1.0)
    SET @tmp2 = 1.0
  IF (@tmp > 1.0 AND @tmp < 5000)
    SET @tmp2 = 5.0
  IF (@tmp >= 5000 AND @tmp < 100000)
    SET @tmp2 = 6.0
  IF(@tmp >=100000 AND @tmp < 200000)
    SET @tmp2 = 7.0
  IF (@tmp >=200000)
    SET @tmp2 = 9.0

  SET @tmp = @tmp * (1.0 -@tmp2)

  -- Indirect data access
  SELECT @return = String1 + String2
  FROM inmemory.Random_Strings_InMemoryHASH  -- We are using inmemory object
  WHERE id = ABS(CAST(@tmp+1 AS INT)%100000 )

  RETURN (@return)
END
Nótese que hemos usado esta vez un objeto preparado para búsquedas de tipo random, con un índice HASH inmemory.Random_Strings_InMemoryHASH

Un ejemplo de uso podría ser

SELECT AVG(LEN((inmemory.DummyCalculationWithDataAccessInMemory(n,n2))))
FROM dbo.Numbers_OnDisk

 

Comparativas de rendimiento

Veamos ahora las comparativas de rendimiento (que se basan en medias de varias ejecuciones):

Performance UDF with data access

En este caso ya parece que las tornas han cambiado bastante respecto al caso de UDF sin acceso a datos. De hecho es totalmente al revés a lo que ocurre cuando codificamos una UDF sin acceso a datos ya que en este caso, el caso peor es el de utilizar SQLCLR y el mejor es el de utilizar InMemory. Concretamente ahora, donde ya no es tan importante el cambio de contexto , como lo que importa es el rendimiento del thread que está ejecutando el código «complejo» de la función la cosa cambia bastante y ahora si que entra en juego el nuevo motor In-Memory aportando sus mejoras de rendimiento.

Comparando los resultados tomando como caso base la función UDF T-SQL:

  • SQLCLR es en esta prueba ~10x veces mas lento
  • InMemory OLTP es ~3.5x mas rápido

Si a esto sumamos que el código UDFInMemory es prácticamente un port copy-paste de la función UDF tradicional…estaremos pensando en que es buena idea hacer uso de esta tecnología :). De hecho una de las premisas que tiene la tecnología InMemory OLTP es que cuanto mas complejo es el código T-SQL, mayor beneficio obtendremos migrando el objeto a InMemory OLTP

El proyecto completo con todos los scripts se encuentran aqui SQLServerScalarUDFPerformance y los tiempos están aquí (excel file)

Enrique Catalá

Enrique Catalá es Mentor en SolidQ, Microsoft Data Platform MVP desde 2013 e Ingeniero en informática. Es Microsoft Certified Trainer (MCT) y está centrado en el motor relacional SQL Server, tanto en la resolución de problemas de rendimiento y escalabilidad en sistemas OLTP como la definición e implementación de entornos de alta disponibilidad confiables, en donde ha llevado con éxito más de 100 proyectos no solo en España, sino en diferentes países como EEUU, Holanda, México, Arabia Saudí, Austria... Es el arquitecto principal de las soluciones para SolidQ llamadas HealthCheck, SQL2Cloud, SCODA y del generador de SSIS de SolidQ. Ponente habitual del SolidQ SUMMIT, miembro y ponente en SQL PASS tanto en España como Iberoamérica ponente en varios SQLSaturday

Latest posts by Enrique Catalá (see all)