Database cursor + while loop within | Performance

[Amr_Mohammad_R]

This function I have created to return all recorded Stock numbers in the Table "ContributorDeeds" the table contains only the start number of the stocks and the end number of the stocks (e.g, from number - to number) I need to get all the numbers between within that range.

The function works fine, however, it tasks several minutes to return the result :(.

Is there a better SQL Query to get the same result?

FUNCTION [dbo].[StockNumbers] () RETURNS @SequenceNumbersOfShares TABLE (StockNumber NVARCHAR(50)) 
AS 
BEGIN 
DECLARE @DeedDocumentID BIGINT DECLARE @MIN NVARCHAR(50) DECLARE @MAX NVARCHAR(50)

--CREATE TABLE #LocalTempTable(StockNumber NVARCHAR(50))

DECLARE db_cursor CURSOR FOR SELECT ContributorDeedsID FROM dbo.ContributorDeeds WHERE DeedFromShare IS NOT NULL AND DeedToShare IS NOT NULL

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @DeedDocumentID 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @MIN = (SELECT DeedFromShare FROM ContributorDeeds WHERE ContributorDeedsID = @DeedDocumentID) 
SET @MAX = (SELECT DeedToShare FROM ContributorDeeds WHERE ContributorDeedsID = @DeedDocumentID) 
WHILE dbo.InsertSP(@MIN, 20) <= dbo.InsertSP(@MAX, 20) 
BEGIN 
INSERT INTO @SequenceNumbersOfShares(StockNumber) VALUES(@MIN) 
SET @MIN = @MIN + 1 
END 
FETCH NEXT FROM db_cursor INTO @DeedDocumentID 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor 
RETURN; 
END;

[Taywin]

It is slow because you first select every record that have from number & to number. Then you iterates through each of the record to find the min and max. Then each iteration, you do insertion...

I would suggest you to do it right at the query level where you could easily get the result right away (add in the WHERE clause).

[Amr_Mohammad_R]

What do you mean? If you mean to make a simple select statement with a where clause with a condition "BETWEEN FROM AND TO" that is good but how? could you explain further please

[isozworld]

Change your cursor option like

DECLARE db_cursor CURSOR local forward_only static FOR

This may your cursor for only local read and take minimum cost for sqlserver

[Amr_Mohammad_R]

I have changed the nested loop within the cursor and now the function looks like this:

FUNCTION [dbo].[StockNumbers] () RETURNS @SequenceNumbersOfShares TABLE (StockNumber BIGINT) 
    AS 
    BEGIN 
    DECLARE @MIN BIGINT 
    DECLARE @MAX BIGINT

    DECLARE db_cursor CURSOR FOR 

    SELECT DISTINCT CAST(DeedFromShare AS BIGINT), CAST(DeedToShare AS BIGINT) 
    FROM    dbo.ContributorDeeds 
    WHERE   DeedFromShare IS NOT NULL AND DeedToShare IS NOT NULL

    OPEN db_cursor 
    FETCH NEXT FROM db_cursor INTO @MIN, @MAX 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    ;WITH TEMP_TABLE AS
    (
    SELECT @MIN AS StockNumber
    UNION ALL
    SELECT StockNumber + 1 FROM TEMP_TABLE
    WHERE StockNumber + 1 <= @MAX
    )
    OPTION (MAXRECURSION 10000)
    SELECT * FROM TEMP_TABLE INTO SequenceNumbersOfShares
    FETCH NEXT FROM db_cursor INTO @MIN, @MAX 
    END 
    CLOSE db_cursor 
    DEALLOCATE db_cursor 
    RETURN; 
    END;

Execution produced more than 94,000 record in approximately 25-28 seconds while the pre-change were in approximately 35-40 seconds.

Is there a better way to decrease the execution time again?

Mr. isozworld, what is the purpose of "local forward_only static" will it affect on the execution time?

Thanks for your help people

[BitBlt]

I actually wasn't able to get your second function to compile, but I tweaked it a tiny bit. Final result looked like this:

(renamed the function so it wouldn't stomp on anyone's original)

create FUNCTION [dbo].[StockNumbers2] () RETURNS @SequenceNumbersOfShares TABLE (StockNumber BIGINT) 
    AS 
    BEGIN 
    DECLARE @MIN BIGINT 
    DECLARE @MAX BIGINT
    DECLARE db_cursor CURSOR FOR 
    SELECT DISTINCT CAST(DeedFromShare AS BIGINT), CAST(DeedToShare AS BIGINT) 
    FROM    dbo.ContributorDeeds 
    WHERE   DeedFromShare IS NOT NULL AND DeedToShare IS NOT NULL
    OPEN db_cursor 
    FETCH NEXT FROM db_cursor INTO @MIN, @MAX 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    ;WITH TEMP_TABLE AS
    (
    SELECT @MIN AS StockNumber
    UNION ALL
    SELECT StockNumber + 1 FROM TEMP_TABLE
    WHERE StockNumber + 1 <= @MAX
    )
    --OPTION (MAXRECURSION 10000)
    insert into @SequenceNumbersOfShares SELECT * FROM TEMP_TABLE 
    FETCH NEXT FROM db_cursor INTO @MIN, @MAX 
    END 
    CLOSE db_cursor 
    DEALLOCATE db_cursor 
    RETURN; 
    END;

I put together a test dataset of 30,000 rows, where 10,000 of the rows had both From and To values...the rest had one or the other NULL.

The difference between the From and To in each row was 100 count.

When I ran the function in SSMS (on a SQL2012 desktop instance) it returned over 1,000,000 rows (as I would expect). Problem is, it ran in less than 14 seconds, and I returned WAY more records than you.

Can you give a little more information about the input data, or your configuration? Maybe even give a snippet of how you're using the function, and the expected result?

[Amr_Mohammad_R]

BitBlt Unfortunately I do not know the configuration and actually I think there is no configuration :(

What is the configuration that may affect the execution time?

I'm working in fixing bugs and somtime modification of a corporation system and such corporation sells deeds and when they sell one the inforamtion include only the start and end of the stocks within the deed (e.g deed no. 3 contains 10 stocks start from 101 to 110) of course beside other information regarding the shareholder and the deed. It does not require that the stock numbers be in sequential (e.g, the deed no. 1 stocks may be from 1 to 10 the deed no.2 stocks may be from 41 to 50 and so on) the user need to know all the number of stocks the corporation sold. So I made this function to pass on each record on the table where the deed information are and read the columns "from" and "to" and get the numbers within the range as the function above shows.

Best Regards,

[Taywin]

Let me clarify the problem first.

You need to get "all" numbers within a range of integer. For example, a set of numbers are 1-10, 14-22, 17-33, 50-100. Your result should show 1-10, 14-33, and 50-100. Is that correct?

[Taywin]

If your problem is supposed to be what I said in my earlier post, a suggestion of how to do it is below...

  Select the record with the lowest min value
  Go into a while loop if the record exists
    Save min value (initilized value)
    Save max value (initilized value)
    Select a record that has min value less than or equal to the saved max value but has the highest max value
    Go into a while loop if the record exists
      Update max value with the new found record
      Select a record that has min value less than or equal to the saved max value but has the highest max value
    End While
    If record exists
      Save max value (may be redundant if not go into the 2nd loop, but OK)
    End IF
    Record all numbers between min & max
    Select the record with the lowest min value which is greater than the saved max value
  End While
  Return

  --------------------
  #Somewhat in pseudo code with SQL commands...

  @REC = SELECT min_value, max_value FROM table_name WHERE ROWNUM<=1 ORDER BY min_value asc
  While @REC exists
    @MIN = @REC(min_value)
    @MAX = @REC(max_value)
    @REC = SELECT max_value FROM table_name WHERE ROWNUM<=1 and min_value<=@MAX ORDER BY max_value desc
    While @REC exist  # at least one record is found
      @MAX = @REC(max_value)
      @REC = SELECT max_value FROM table_name WHERE ROWNUM<=1 and min_value>=@MAX ORDER BY max_value desc
    END
    if @REC exist
      @MAX = @REC(max_value)
    END
    record_all_numbers_in_range(@MIN, @MAX)
    @REC = SELECT min_value, max_value FROM table_name WHERE ROWNUM<=1 and min_value>@MAX ORDER BY min_value asc
  END

The algorithm is to first find the lowest from number that contain the highest to number. Now we have a tentative range with actual from number. Use the to number to find a record that could have the number in between its from and to. If found the record, update the to number and reuse the number to find another record using the same approach. Once no other record is found, the number either the highest to number of the table or it is just the highest to number of the range. Save all numbers between the found from and to. Then use the to number again to find the next starting range. If found, restart the loop; otherwise, you found all numbers in the range.

Example, given a set of record from-to: 1-10, 14-22, 17-33, 19-44, and 50-100. The first round would give you 1-10. The second round (goes into the 2nd loop) would give you 14-44. And the last round would give you 50-100.

Hope this help...