poor performance function

Question

poor performance function

Tomas Martin

SSC Enthusiast

Points: 114
More actions
December 1, 2020 at 4:54 pm

#3815637
Hello friends.
This is my table "Cuentas"
```
[IdCuenta] [int] IDENTITY(1,1) NOT NULL,
[IdJerarquia] [int] NOT NULL,
[Cuenta] [varchar](20) NOT NULL
```
and I use this function
```
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
```
I need, given an 'IdCuenta', locate the id of the immediately preceding one according to the 'Cuenta' column and that it is contained in the same string. I mean, for example:
with this data
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
this results
for idcuenta=384196 ('120') return value = 384195 ('12')
for idcuenta=385257 ('12000100 ') return value = 384198 ('12000')
for idcuenta=473232 ('1200000100000 ') return value =473231 ('12000001')
for idcuenta=386491 ('1200010100000 ') return value = 386490 ('12000101')
That is, returns that begins the same as the one given but has less length.
This function is used in multiple views and sp and works fine but with poor performance when the table has many records, several hundred thousand. The execution plan penalizes the ORDER BY.
Any suggestions or alternatives to optimize this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

The easiest way is to upgrade to SQL 2019 and put the database in compatibility mode 150. I would expect in that case that the function will be inlined which will permits for big optimizations. On older versions, it is called for every row, for which you are paying a big penalty.

If upgrading to SQL 2019 is not an option, you can rewrite it as a table-valued function which you invoked through OUTER APPLY. That is rather than saying

SELECT dbo.fidCuentaPadre(col) FROM tbl

you say

SELECT f.retvalue FROM tbl 
OUTER APPLY dbo.fidCuentaPadre(tbl.col)  f

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Sergiy SSC Guru Points: 110208 More actions · Answer 2

What indexes do you have on the table "Cuentas"?

_____________
Code for TallyGenerator

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

If you don't already have similar indexes try these two indexes and see if there is an improvement:

create index IX_Cuentas_1 on dbo.Cuentas(idCuenta) INCLUDE (idjerarquia,Cuenta)
create index IX_Cuentas_2 on dbo.Cuentas(IdJerarquia,Cuenta) INCLUDE (IdCuenta)