Slow MSSQL stored procedure in excel processes with 30,000 rows

Question

I have a web application with iterface on which users can add files. Data from the excel file is collected, combined, and passed to a stored procedure that processes and returns data. A brief description of the stored procedure.

The stored procedure collects the string, splits it with a separator, and saves it in the temp variable table.

Another process is done through the temp table, where counting is done to find an exact match and an approximate hit counter by comparing each row again displays a view that contains all the names to compare for each row in the first

An exact hit counter is where, for example, the string found is found in the form ... (Bobby Bolonsky) An approximate match is performed using the Levenshtein algorithm database function with a frequency of 2.
temo table @ temp1.

The result (name, exact quantity counter and approximate hit counter) are stored in the last pace table.

in the last temp table, the select statement is run, which returns all the data to the application.

My problem is that when I transferred huge files, such as an excel file with 27,000 names. It took 2 hours to process and return data from the IT database.

, , . . 15% . , 15%.

, , .

, -.

CREATE PROCEDURE [dbo].[FindMatch]
    @fullname varchar(max),@frequency int,
    @delimeter varchar(max) AS    

    set @frequency = 2

    declare @transID bigint

    SELECT @transID = ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) 

    DECLARE @exactMatch int = 99
    DECLARE @approximateMatch int = 99
    declare @name varchar(50)
    DECLARE @TEMP1 TABLE (fullname varchar(max),approxMatch varchar(max), exactmatch varchar(max))

    DECLARE @ID varchar(max)

    --declare a temp table
     DECLARE @TEMP TABLE (ID int ,fullname varchar(max),approxMatch varchar(max), exactmatch varchar(max))
     --split and store the result in the @temp table
     insert into @TEMP (ID,fullname) select * from fnSplitTest(@fullname, @delimeter)

     --loop trough the @temp table
     WHILE EXISTS (SELECT ID FROM @TEMP)
     BEGIN
        SELECT Top 1 @ID = ID FROM @TEMP 
        select @name = fullname from @TEMP where id = @ID 


          --get the exact match count of the first row from the @temp table and so on until the loop ends
          select @exactMatch = count(1) from  getalldata where  replace(name,',','') COLLATE Latin1_general_CI_AI =  @name COLLATE Latin1_general_CI_AI

        --declare temp @TEMP3
        DECLARE @TEMP3 TABLE (name varchar(max))


        --insert into @temp 3 only the data that are similar to our search name so as not to loop over all the data in the view
        INSERT INTO @TEMP3(name) 
        select  name from getalldata where  SOUNDEX(name) LIKE SOUNDEX(@name) 

        --get the approximate count using the [DEMLEV] function. 
        --this function uses the Damerau levenshtein distance algorithm to calculate the distinct between the search string
        --and the names inserted into @temp3 above. Uses frequency 2 so as to eliminate all the others
        select @approximateMatch = count(1) from @TEMP3 where
        dbo.[DamLev](replace(name,',',''),@name,@frequency) <= @frequency and 
        dbo.[DamLev](replace(name,',',''),@name,@frequency) > 0  and name != @name


        --insert into @temp1 at end of every loop results
          insert into  @TEMP1 (fullname,approxMatch, exactmatch) values(@name,@approximateMatch,@exactMatch)
        insert into FileUploadNameInsert (name) values (@name + ' ' +cast(@approximateMatch as varchar) + ' ' + cast(@exactMatch as varchar) + ', ' + cast(@transID as varchar)  )
        DELETE FROM @TEMP WHERE ID= @ID
        delete from @TEMP3
    END

    --Return all the data stored in @temp3
    select fullname,exactmatch,approxMatch, @transID as transactionID from @TEMP1

GO

Answer 1

-,

• Openrowset , .

• , , .

15 30 000 .