SQL Server - Optimizar función

 
Vista:
Imágen de perfil de TOMÁS
Val: 5
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

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:

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
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Isaias
Val: 3.521
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Optimizar función

Publicado por Isaias (4240 intervenciones) el 01/12/2020 02:47:20
¿Cuál es su versión de SQL Server y Edicion?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de TOMÁS
Val: 5
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Optimizar función

Publicado por TOMÁS (2 intervenciones) el 01/12/2020 09:31:25
2008 R2 y superiores
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 3.521
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Optimizar función

Publicado por Isaias (4240 intervenciones) el 01/12/2020 18:03:56
Verifique si esta solución de alternativa a LAG en 2008 le funciona:

https://stackoverflow.com/questions/22188514/alternate-of-lead-lag-function-in-sql-server-2008
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar