Stored Procedures en Entity Framework Core

Un tema que no se trata mucho últimamente es el uso de Stored Procedures en SQL, porque se ignora su existencia, lo cual no siempre es la mejor de las ideas pues en ciertas situaciones pueden ser la mejor solución.

1 - Qué es un Stored Procedure o procedimiento almacenado? 

Un stored Procedure (SP) no es más que un conjunto de instrucciones SQL que se ejecutan en la base de datos. Es la forma de agrupar instrucciones que en nuestro aso vamos a ejecutar desde código, pero puden ser ejecutadas desde la propia base de datos o cualquier sistema con acceso.

Lo que nos importa aquí es saber que dentro de un stored procedure podemos tener una o más instrucciones SQL. 

Y un stored procedure puede tanto recibir parámetros como devolver un resultado.

2 - Los Stored Procedure están en desuso

Algo que habrás notado si trabajas en empresas es que apenas se utilizan stored procedure, la gran mayoría de interacciones con la base de datos se hacen directamente desde el código, ya bien sea con ORMs (Entity framework core/Dapper en C#, ActiveRecord en Ruby, JPA/Hibernate en java, etc) o escribiendo el SQL en código y utilizando el conector de tu lenguaje.

Esto se hace porque hoy en día las bases de datos corren en servidores que son más que capaces de ejecutar todo lo que necesitamos y más. No solo eso, sino que aumentar el servidor en caso de que en ciertas horas del día vaya un poco más justo tampoco cuesta mucho en relación al coste total de tener la empresa funcionando. 

Así que las empresas, con buen criterio en mi opinión, eligen la velocidad de desarrollo que te da un ORM frente a una oposición de pagar 300€/mes por un servidor con 32gb de ram y 16vCPU. Ya que en la gran mayoría de empresas un servidor así es más que suficiente.

Un servidor así te puede dar fácilmente más de 150-200 mil lecturas por segundo (si son por Id) y unas 10-15 mil inserciones. Este sería un ejemplo para apps que hacen CRUDs básicos.

Con esos números, el precio de la base de datos no debería ser un problema, especialmente si eres una empresa que da servicios a otras empresas donde normalmente pagan por uso o una subscripción.

Pero claro, la gran mayoría no son todas las empresas hay empresas que debido a la naturaleza de los datos, ya bien sea por la cantidad o por la estructura de los mismos el costo de la base de datos es mucho mayor y cualquier mejora de rendimiento, por pequeña que pueda ser, tiene un gran impacto a largo plazo. 

NOTA: Yo trabajé en una empresa donde teníamos una base de datos que costaba 3 mil euros diarios,  (si, cada día).

3 - Cuándo utilizar un stored Procedure?

Como acabo de decir, utilizar Stored Procedures es muy importante para todas aquellas empresas que tienen mucho uso y donde pequeñas mejoras de rendimiento van a tener un impacto significativo a largo plazo. 

Hay varios motivos para que esta afirmación sea una realidad, SQL es un lenguaje de verdad, está optimizado a los extremos y básicamente es lo más rápido que un lenguaje puede ser, especialmente si sabes cuando y donde poner indexes. El puesto de desarrollador SQL existe y existe por algo.

Los SPs te permiten hacer única y exclusivamente lo que necesites y devolver única y exclusivamente lo que necesites, lo que hace que la transferencia de red sea menor, algo importante a largo plazo si tiene mucho uso.

Pese a que para mi ese es el principal motivo, también tenemos el tema de la seguridad, podemos asignar permisos por stored Procedure, lo que quiere decir que en cada aplicación vas a tener un usuario distinto que se conecte a la base de datos y ese usuario tendrá permisos para ciertos SPs.

Obviamente los permisos se pueden hacer por tabla, pero es mucho más sencillo administrar permisos en Stored Procedures que por tablas o incluso columnas. 

Finalmente lo que yo he notado es que cuando tienes TODO el código por SQL es mucho más fácil detectar donde algo sucede, me explico. Imagínate que tienes un bug donde cierta columna tiene un valor que no debería. 

Llegados a este punto tienes que buscar dónde y porqué ese cambio ha sucedido, el primer paso es buscar el donde puede suceder. 

Si tienes todo en Stored Procedures vas a tener uno o varios que actualicen este valor, únicamente tienes que buscar su uso en los repositorios de la empresa, lo cual será sencillo pues tienen nombres únicos, y de ahí ya es analizar el código. 

Si no tienes stored procedures, tienes que hacer lo mismo, PERO buscando directamente en el código, lo cual puede llevarte muchas más horas ya que es más difícil buscar una aguja en un pajar (todos los repositorios de la empresa) que en una tienda de costura (el repo de SQL).

Quiero hacer un inciso aquí y es que hace poco leí un tweet de alguien que decía que los ORM se habían cargado la optimización que con ORM nunca sabes si tienes un problema de optimización… Bueno, esta afirmación no puede ser más errónea. 

Tanto si utilizas un ORM o no, en la base de datos vas a tener constancia de qué consulta, cuándo se está ejecutando y de todas sus características, es más fácil de optimizar si está en un stored Procedure, desde luego, pero no está perdida en el limbo.

4 - Debemos utilizar Stored procedures? 

Pese a que he puesto en muy buena fama a los stored Procedures no hay que olvidar que su uso hace que el desarrollo de las aplicaciones sea mucho más lento, ya que es una capa extra que debemos administrar y mantener y eso siempre cuenta. 

Ignorando temas de seguridad y lo mencionado anteriormente de que sea más fácil o más conveniente en ciertas situaciones, el uso o no de los stored procedures radica principalmente en dos cuestiones principales. 

El número de consultas por segundo que tu sistema recibe (no una única aplicación sino el conjunto) y segundo, cuán complejas son estas consultas. 

Me explico, para un select * por ID te va a dar igual utilizar un store procedure o no, no vas a notar diferencia de rendimiento, tengas una llamada o 100mil por segundo.

Donde si vas a notar diferencias es a la hora de hacer consultas complejas o incluso tener lógica en dichos Stored Procedures, no es lo ideal, pero depende de la situación puede ser que necesites tener lógica dentro de SQL. Es muy común ver escenarios donde en SQL una consulta tarda 4-5 segundos, lo cual es muchísimo, y la equivalente en código tarda cerca de un minuto, simplemente por la complejidad y todo lo que se hace con los datos. 

Finalmente, Si que es verdad que Entity Framework ha mejorado con las últimas versiones y ya no es tan ineficiente a la hora de hacer joins, aún así no deja de ser código generado automáticamente por lo que en mi opinión, cuando hay más de dos joins siempre debemos hacer la consulta de forma manual, ya sea con un Stored Procedure o enviando la consulta de forma manual a través de Entity Framework.

5 - Stored procedures en Entity Framework Core

Aunque si bien es cierto que no necesitamos utilizar Entity Framework Core para utilizar stored procedures, este nos da la posibilidad, algo que es muy útil cuando tienes situaciones donde el 90% de tu código son CRUDs básicos pero tienes un 10% con consultas muy complejas. 

Una cosa a tener en cuenta de los stored procedures es que da igual si los ejecutas en dentro de Unit of Work o no, los SPs son atómicos y como tales se ejecutan de forma individual.

Para ver su funcionamiento desde Entity Framework, lo primero que debemos hacer es crear un store procedure directamente en SQL:

CREATE OR REPLACE FUNCTION get_user_by_id(user_id INT)
RETURNS TABLE (
    "Id" INT,
    "UserName" TEXT,
    "Email" VARCHAR,
    "IsDeleted" BOOLEAN,
    "DeletedTimeUtc" TIMESTAMPTZ,
    "LastUpdateUtc" TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT u."Id", u."UserName", u."Email", u."IsDeleted", u."DeletedTimeUtc", u."LastUpdateUtc"
    FROM "Users" u
    WHERE u."Id" = user_id;
END;
$$ LANGUAGE plpgsql;

Nota: Cada base de datos tiene un formato diferente para los store procedures, te recomiendo que investigues cual es el tuyo, en nuestro caso ese ejemplo es para PostgreSQL, este sería el equivalente en MySQL:

DELIMITER //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT Id, UserName, Email, IsDeleted, DeletedTimeUtc, LastUpdateUtc
    FROM Users
    WHERE Id = user_id;
END //

DELIMITER ;

Como puedes ver, la diferencia es únicamente de sintaxis, pero eso hace que no podamos usar unos en los otros.

Para llamarlo desde la propia base de datos,en postgres hacemos un select de dicha función:

select * from get_user_by_id(1);

Ahora vamos a pasar a la sección donde llamamos a dicho SP desde el código.

Recordamos que en este proyecto estamos usando el patrón repositorio y no solo eso, sino que tenemos un repositorio genérico que contiene la mayoría del código CRUD. 

Lo que vamos a hacer es sobreescribir el método GetById para llamar al Stored Procedure, que tenemos que hacerlo con sql puro como vimos en un post anterior.

public override async Task<User?> GetById(int id)
        => await Entities
            .FromSqlInterpolated($"SELECT * FROM get_user_by_id({id})")
            .FirstOrDefaultAsync();

Una pequeña nota antes de terminar; Si tienes el 98% de las consultas con Entity framework no pasa nada por tener la invocación de uno o dos stored procedures así. Pero, si tienes el 98% del código con Stored procedures la recomendación es utilizar el Driver de tu base de datos directamente en vez de EF, algo similar a esto:

using (var conn = new NpgsqlConnection(connectionString))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand("SELECT * FROM get_user_by_id(@user_id)", conn))
    {
        cmd.Parameters.AddWithValue("user_id", userId);

        using (var reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                int id = reader.GetInt32(reader.GetOrdinal("Id"));
                string userName = reader.GetString(reader.GetOrdinal("UserName"));
                string email = reader.GetString(reader.GetOrdinal("Email"));
                bool isDeleted = reader.GetBoolean(reader.GetOrdinal("IsDeleted"));
                DateTime? deletedTimeUtc = reader.IsDBNull(reader.GetOrdinal("DeletedTimeUtc")) ? (DateTime?)null : reader.GetDateTime(reader.GetOrdinal("DeletedTimeUtc"));
                DateTime lastUpdateUtc = reader.GetDateTime(reader.GetOrdinal("LastUpdateUtc"));

               return new user(id, username, email, isDeleted, deletedTimeUtc, lastUpdateUtc);
            }
            else
            {
                return null;
            }
        }
    }
}