Optimize module 10 SQL Server

Question

I have a database created by apróx. a decade, this same bank has several records and obviously, by being in production, can not be changed from day to night.

The problem is:

The products registered in the bank have serials, these serials at the beginning of everything there was no need to record the check digit, however, nowadays the digit is necessary to generate reports and everything else.

To calculate this digit makes up module 10 with the 19 serial numbers, to perform this calculation, we have a function of the SQL server that initiates a while to break and add the serial digits. Other while to perform the necessary calculations and then a if, to return the equivalent number in delta.

Below the function:

DECLARE @delta AS TABLE ( indice INT, valor INT )

  INSERT  INTO @delta
          ( indice, valor )
  VALUES  ( 0, 0 ),
          ( 1, 1 ),
          ( 2, 2 ),
          ( 3, 3 ),
          ( 4, 4 ),
          ( 5, -4 ),
          ( 6, -3 ),
          ( 7, -2 ),
          ( 8, -1 ),
          ( 9, 0 )

DECLARE @serie VARCHAR(50) = '############'
DECLARE @deltaindex INT
DECLARE @deltavalue INT
DECLARE @soma INT = 0
DECLARE @i INT = 1

WHILE ( @i <= LEN(@serie) )
    BEGIN
        SET @soma = @soma + CAST(SUBSTRING(@serie, @i, 1) AS INT)
        SET @i = @i + 1
    END
SET @i = LEN(@serie)
WHILE ( @i > 0 )
    BEGIN
        SET @deltaindex = CAST(SUBSTRING(@serie, @i, 1) AS INT)
        SELECT  @deltavalue = valor
        FROM    @delta
        WHERE   indice = @deltaindex
        SET @soma = @soma + @deltavalue
        SET @i = @i - 2
    END
DECLARE @mod10 INT
SET @mod10 = @soma % 10
SET @mod10 = 10 - @mod10
IF ( @mod10 = 10 )
    BEGIN
        SET @mod10 = 0
    END
PRINT @mod10

That being said, we have approx. 1.5KK of records that need to be displayed in a report and each record has between 1 and 2 serials and this per serial query ends up delaying quite the display of the report, enough so that I can not issue a report by the system much less by the bank, taking around 1H30 to issue and even so I had to cancel the query.

I wonder if there is any way to optimize the module of 10 so that the consultation does not delay so much.

Answer 1

Table variables are stored on disk, in the tempdb database, as well as the temporary tables. Then, it may be that low performance is caused by using the @delta table variable for calculations that can be implemented by a mathematical expression. Another factor is that scalar type functions degrade performance by nature. Functions are more efficient when they are of the type inline table-Valued.

Here is a suggested optimization. The @delta table has been deleted and replaced by a very simple mathematical calculation. Evaluate the performance and correction of the suggestion, as I had no way to test. If there is an error in the code, let us know so that we can correct.

-- código #1 v3
declare @serie varchar(50) = '1234567890123456789';
declare @deltaindex int, @deltavalue int, @soma int, @i int, @tamserie int;
set @tamserie = len(@serie);

set @i = 1;
set @soma = 0;
while ( @i <= @tamserie )
    begin
    set @soma = @soma + cast(substring(@serie, @i, 1) as int);
    set @i = @i + 1;
    end;

set @i = @tamserie;
while ( @i > 0 )
    begin
    set @deltaindex = cast(substring(@serie, @i, 1) as int);
    set @deltavalue= case when (@deltaindex <= 4) then @deltaindex
                          else ((9 - @deltaindex) * -1) end;
    set @soma = @soma + @deltavalue;
    set @i = @i - 2;
    end;

declare @mod10 int;
set @mod10 = 10 - (@soma % 10);
IF ( @mod10 = 10 )
    set @mod10 = 0;

PRINT @mod10;