TSQL - Empty rows returned in a cursor

Question

apologies as this may be a very dumb question -- I am working with a cursor it joins a temporary table of about 697K rows and a function that returns 78K rows. (this i should add is an improvement it used to be worse). The cursor goes through and matches on two values in both 'tables' and updates a third. This takes 6 or so hours. Which is absurd. We are trying to come up with ways to make this more efficient.

Any and all suggestions are appreciated. But my inquiry is this --

It seems to be returning data that looks as follows (in a lot of cases empty information). Can I restrict the code to say where ... data isn't null ... but its not that its coming back Null its empty/nonexistent rows. I am thinking if there is a way to exclude rows like that we may limit our data pool. But I don't entirely know what this means.

declare @season int = 21

DECLARE @match varchar(55)
declare @perf_no int
declare @order_dt datetime

DECLARE   @price CURSOR
SET       @price = CURSOR FOR
SELECT    distinct match_criteria, perf_no, order_dt
FROM      #prices
OPEN      @price
FETCH NEXT
FROM      @price INTO @match, @perf_no, @order_dt
WHILE     @@FETCH_STATUS = 0
BEGIN

select  @match, @perf_no, @order_dt, x.price as 'amount'
from    #prices p
join    dbo.[LFT_GET_PRICES_Seasonal] (@season, @order_dt) x on p.perf_price_type = x.perf_price_type and p.zone_no = x.zone_no
where   match_criteria = @match and perf_no = @perf_no

FETCH NEXT
FROM @price INTO @match, @perf_no, @order_dt
END
CLOSE @price
DEALLOCATE @price

Here is sample of what #prices and # our function returns.

prices

pkg_no  perf_no zone_no price_type  order_dt                    price   perf_price_type match_criteria
12      144     2707    1073        2018-09-03  00:00:00.000    NULL    115769          O5716788P1517Z2707
12      123     2707    1073        2018-09-03  00:00:00.000    NULL    115840          O5716788P1517Z2707
12      887     2707    1073        2018-09-03  00:00:00.000    NULL    115521          O5716788P1517Z2707

Function:

perf_price_type zone_no price   min_price   enabled editable_ind
115521          2678    12.00   12.00       Y       N
115521          2679    61.00   61.00       Y       N
115521          2680    41.00   41.00       Y       N

What the cursor does is update the price in the #prices table based on that of the function. (we used the cursor to only limit it to certain performance/limited criteria). But i'm open to suggestions. and advice as to how to improve this.

Answer 1

You wrote you want to update the #price table with the results from the table valued function.
You can do that by using cross apply instead of a cursor. Since you didn't post proper sample data I have no way of testing my answer, but if it does what you need it should be doing that lightning-fast compared to a cursor.

DECLARE @season int = 21

UPDAET p
SET price = x.Price
FROM #Prices p
CROSS APPLY  
(
    SELECT * 
    FROM dbo.LFT_GET_PRICES_Seasonal(@season, order_dt) udf
    WHERE udf.perf_price_type = p.perf_price_type 
    AND udf.zone_no = p.zone_no
) x

SQL works best with a set based approach and not a procedural approach, which is why you want to avoid loops and cursors whenever possible and only use them as a last resort.