Optimizar función
Publicado por TOMÁS (2 intervenciones) el 30/11/2020 09:43:19
Hola amigos, me estreno con esta consulta:
Tengo una tabla 'Cuentas' con este diseño (reducido)
[IdCuenta] [int] IDENTITY(1,1) NOT NULL,
[IdJerarquia] [int] NOT NULL,
[Cuenta] [varchar](20) NOT NULL
Necesito, dado un 'IdCuenta' localizar el id de la inmediatamente anterior según la columna 'Cuenta' y que esté contenida en la misma cadena. O sea, si tengo
IdCuenta IdJerarquia Cuenta
----------- ----------- --------------------
384194 6 1
384195 6 12
384196 6 120
384197 6 1200
384198 6 12000
473231 6 12000001
473232 6 1200000100000
385257 6 12000100
385258 6 1200010000000
386490 6 12000101
386491 6 1200010100000
395271 6 12000102
395272 6 1200010200000
para la cuenta 120 tengo que encontrar 12
para la cuenta 12000100 tengo que encontrar 12000
para la cuenta 1200000100000 tengo que encontrar 12000001
para la cuenta 1200010100000 tengo que encontrar 12000101
Es decir la que comience igual que la dada pero tenga menos longitud.
Para ello he creado esta función:
Esta función se usa en varias vistas y sp y funciona correctaenmte pero con un mal rendimiento cuando la tabla tiene muchos registros, varios cientos de miles.
El plan de ejecución me penaliza el ORDER BY.
¿Alguna sugerencia o alternativa para optimizar esto?
Tengo una tabla 'Cuentas' con este diseño (reducido)
[IdCuenta] [int] IDENTITY(1,1) NOT NULL,
[IdJerarquia] [int] NOT NULL,
[Cuenta] [varchar](20) NOT NULL
Necesito, dado un 'IdCuenta' localizar el id de la inmediatamente anterior según la columna 'Cuenta' y que esté contenida en la misma cadena. O sea, si tengo
IdCuenta IdJerarquia Cuenta
----------- ----------- --------------------
384194 6 1
384195 6 12
384196 6 120
384197 6 1200
384198 6 12000
473231 6 12000001
473232 6 1200000100000
385257 6 12000100
385258 6 1200010000000
386490 6 12000101
386491 6 1200010100000
395271 6 12000102
395272 6 1200010200000
para la cuenta 120 tengo que encontrar 12
para la cuenta 12000100 tengo que encontrar 12000
para la cuenta 1200000100000 tengo que encontrar 12000001
para la cuenta 1200010100000 tengo que encontrar 12000101
Es decir la que comience igual que la dada pero tenga menos longitud.
Para ello he creado esta función:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE FUNCTION [dbo].[fIdCuentaPadre] (@IdCuenta as integer)
RETURNS integer
AS
BEGIN
DECLARE @IdJerarquia INT
DECLARE @Cuenta VARCHAR(20)
SELECT @IdJerarquia = idjerarquia, @Cuenta=Cuenta FROM cuentas WHERE idCuenta = @idCuenta
RETURN (SELECT TOP(1) IdCuenta FROM dbo.Cuentas
WHERE (IdJerarquia = @IdJerarquia)
AND (@Cuenta LIKE Cuenta + '%')
AND (LEN(Cuenta) < LEN(@Cuenta))
ORDER BY LEN(Cuenta) DESC
)END
Esta función se usa en varias vistas y sp y funciona correctaenmte pero con un mal rendimiento cuando la tabla tiene muchos registros, varios cientos de miles.
El plan de ejecución me penaliza el ORDER BY.
¿Alguna sugerencia o alternativa para optimizar esto?
Valora esta pregunta
0
