Running Total until specific condition is true

Question

I'm having a table representing the dealers cards and their rank. I'm now trying to make a query (as fast as possible) to set status on the game.

(As said before, only the dealer cards is shown)
W = Win
S = Stand
L = Loss
B = Blackjack (in two cards)

About the rules: The dealer wins at 21, if it's in two cards its blackjack. If the rank is between 17 and 20 it's S = stand. Over 21 is a loss.

Ranks:

1 (ACE) - 1 or 11 rank. Counted as 11.

2-10 - 2-10 rank

11-13 (knight - king) - 10 rank

╔════╦══════╦════════╗
║ Id ║ Rank ║ Status ║
╠════╬══════╬════════╣
║  1 ║    1 ║        ║
║  2 ║    5 ║        ║
║  3 ║    8 ║ L      ║  //24 = Loss
║  4 ║    3 ║        ║
║  5 ║    1 ║        ║
║  6 ║    7 ║ W      ║  //21 = Win
║  7 ║   10 ║        ║
║  8 ║    1 ║ B      ║  //21 = Blackjack
║  9 ║   10 ║        ║
╚════╩══════╩════════╝

I've tried to use a counter to check if it's blackjack and then I'm using a "RunningPoint" to check the sum of the cards.

I have now a solution bad it shows very bad performance when it's a lot of data. How would you do this and what can I do to optimize my query? When using more data I also need to use option (maxrecursion 0)

(When having 1 million rows it's not even possible to run this...)

My example: http://sqlfiddle.com/#!6/3855e/1

Answer 1

There's no efficient solution using plain SQL (including Windowed Aggregate Functons), at least nobody found one, yet :-)

Your recursive query performs bad because it's way too complicated, this is a simplified version:

Edit: Fixed the calculation (Fiddle)

WITH ctePoints AS
 (
   SELECT 1 AS id
        ,rank
        ,CASE 
           WHEN rank >= 10 THEN 10
           WHEN rank = 1 THEN 11
           ELSE rank
         END AS Point
        ,1 AS Counter
   FROM dbo.BlackJack
   WHERE Id = 1

   UNION ALL

   SELECT t2.Id
        ,t2.rank
        ,CASE WHEN t1.Point < 17 THEN t1.Point ELSE 0 END 
         + CASE 
             WHEN t2.rank >= 10 THEN 10
             WHEN t2.rank = 1 THEN 11
             ELSE t2.rank
           END AS Point
        ,CASE WHEN t1.Point < 17 THEN t1.Counter + 1 ELSE 1 END AS Counter
   FROM dbo.BlackJack AS t2
   INNER JOIN ctePoints AS t1 ON t2.Id = t1.Id + 1
 ) 
SELECT ctepoints.*
     ,CASE 
        WHEN Point < 17 THEN ''
        WHEN Point < 20 THEN 'S'
        WHEN Point > 21 THEN 'L'
        WHEN Point = 21 AND Counter = 2 THEN 'B'
        ELSE 'W' 
      END AS DealerStatus            
FROM ctePoints

It's probably still too slow, because it processes row by row.

I usually use recursive SQL to replace cursor logic (because in my DBMS it's usually much faster) but a cursor update might actually be faster (Demo):

CREATE TABLE #BlackJack
(
   id INT PRIMARY KEY CLUSTERED
  ,Rank INT
  ,DealerStatus CHAR(1)
);

insert into #BlackJack (Id, Rank)
values 
(1, 1),(2, 5), (3, 8), (4, 3), (5, 1), (6, 7), (7, 10), (8, 1),(9, 10), (10, 10), (11,1);


DECLARE @Counter INT = 0
        ,@Point INT = 0
        ,@id int
        ,@Rank int
        ,@DealerStatus char(1)

DECLARE c CURSOR
FOR
SELECT id, Rank
FROM #BlackJack 
ORDER BY id FOR UPDATE OF DealerStatus

OPEN c

FETCH NEXT FROM c INTO @id, @Rank

WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @counter = @counter + 1

    SET @Rank = CASE
                  WHEN @Rank >= 10 THEN 10
                  WHEN @Rank  = 1  THEN  11
                  ELSE @Rank
                END 

    SET @Point = @Point + @Rank

    SET @DealerStatus = CASE 
                          WHEN @Point < 17 THEN ''
                          WHEN @Point < 20 THEN 'S'
                          WHEN @Point > 21 THEN 'L'
                          WHEN @Point = 21 AND @Counter = 2 THEN 'B'
                          ELSE 'W' 
                        END 

    IF @Point >= 17 
    BEGIN
      UPDATE  #BlackJack 
      SET DealerStatus = @DealerStatus
      WHERE CURRENT OF c;

      SET @Point = 0

      SET @Counter = 0
    END

    FETCH NEXT FROM c INTO @id, @Rank
  END

CLOSE c
DEALLOCATE c

SELECT * FROM #BlackJack ORDER BY id

Still @lad2025's "quirky update" is the fastest way to get the expected result, but it's using an undocumented feature and if a Service Pack breaks it there's no way to complain about it :-)