poor performance function

  • 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?

     

     

     

  • 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]

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

  • 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)

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

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