Optimize module 10 SQL Server

Question

Optimize module 10 SQL Server

Navigation

#1 by (2 votes)

1

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

The problem is:

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

In order to calculate this digit, it is a module of 10 with the 19 numbers of the serial, to perform this calculation, we have a SQL server function that starts a while to break and add the digits of the serial. Another while to perform the required 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 said, we have approx. 1.5KK of records that need to be displayed in a report and each record has between 1 and 2 serial and this query by serial ends up quite delaying the display of the report, enough that I can not issue a report by the system much less by the bank , taking around 1H30 to issue and even then I had to cancel the query.

I would like to know if there is any way to optimize the 10-module so that the query does not go so far.

sql sql-server query

asked by anonymous 04.12.2017 / 19:18

1 answer

JavaScript - Content of a TD comparing with a variable How to update Gem on windows?

score 2 · Accepted Answer

Table variables are stored on the disk in the tempdb database, as are temporary tables. So 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 functions degrade performance by nature. Functions are more efficient when they are inline table-valued type.

Here is the optimization tip. The @delta table was deleted and replaced with a very simple mathematical calculation. Evaluate the performance and the correctness of the suggestion, because I had no way to test. If there are any errors in the code, let us know so we can correct it.

-- 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;