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:
[box type=”info”] 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[/box]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);
}
[box type=”info”] 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). [/box]
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
[box type=”info”] Nótese que hemos usado esta vez un objeto preparado para búsquedas de tipo random, con un índice HASH inmemory.Random_Strings_InMemoryHASH[/box]
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):
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
[box type=”download”] El proyecto completo con todos los scripts se encuentran aqui SQLServerScalarUDFPerformance y los tiempos están aquí (excel file)[/box]