CODEWITHSUNDEEP
sqlsql-servert-sqldelaywait
Simmo33
Simmo33

Reputation: 73

WAITFOR DELAY doesn't act separately within each WHILE loop

I've been teaching myself to use WHILE loops and decided to try making a fun Russian Roulette simulation. That is, a query that will randomly SELECT (or PRINT) up to 6 statements (one for each of the chambers in a revolver), the last of which reads "you die!" and any prior to this reading "you survive."

I did this by first creating a table #Nums which contains the numbers 1-6 in random order. I then have a WHILE loop as follows, with a BREAK if the chamber containing the "bullet" (1) is selected (I know there are simpler ways of selecting a random number, but this is adapted from something else I was playing with before and I had no interest in changing it):

SET NOCOUNT ON

CREATE TABLE #Nums ([Num] INT)
DECLARE @Count INT = 1
DECLARE @Limit INT = 6
DECLARE @Number INT

WHILE @Count <= @Limit
BEGIN
SET @Number = ROUND(RAND(CONVERT(varbinary,NEWID()))*@Limit,0,1)+1
IF NOT EXISTS (SELECT [Num] FROM #Nums WHERE [Num] = @Number)
BEGIN
INSERT INTO #Nums VALUES(@Number)
SET @Count += 1
END
END

DECLARE @Chamber INT

WHILE 1=1
BEGIN
  SET @Chamber = (SELECT TOP 1 [Num] FROM #Nums)
    IF @Chamber = 1
    BEGIN
      SELECT 'you die!' [Unlucky...]
      BREAK
    END
  SELECT
   'you survive.' [Phew...]
  DELETE FROM #Nums WHERE [Num] = @Chamber
END

DROP TABLE #Nums

This works fine, but the results all appear instantaneously, and I want to add a delay between each one to add a bit of tension.

I tried using WAITFOR DELAY as follows:

WHILE 1=1
BEGIN
WAITFOR DELAY '00:00:03'
  SET @Chamber = (SELECT TOP 1 [Num] FROM #Nums)
    IF @Chamber = 1
    BEGIN
      SELECT 'you die!' [Unlucky...]
      BREAK
    END
  SELECT
   'you survive.' [Phew...]
  DELETE FROM #Nums WHERE [Num] = @Chamber
END

I would expect the WAITFOR DELAY to initially cause a 3 second delay, then for the first SELECT statement to be executed and for the text to appear in the results grid, and then, assuming the live chamber was not selected, for there to be another 3 second delay and so on, until the live chamber is selected.

However, before anything appears in my results grid, there is a delay of 3 seconds per number of SELECT statements that are executed, after which all results appear at the same time. I tried using PRINT instead of SELECT but encounter the same issue.

Clearly there's something I'm missing here - can anyone shed some light on this?

Upvotes: 2

Views: 4136

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

It's called buffering. The server doesn't want to return an only partially full response because most of the time, there's all of the networking overheads to account for. Lots of very small packets is more expensive than a few larger packets1.

If you use RAISERROR (don't worry about the name here where we're using 10) you can specify NOWAIT to say "send this immediately". There's no equivalent with PRINT or returning result sets:

SET NOCOUNT ON

CREATE TABLE #Nums ([Num] INT)
DECLARE @Count INT = 1
DECLARE @Limit INT = 6
DECLARE @Number INT

WHILE @Count <= @Limit
BEGIN
SET @Number = ROUND(RAND(CONVERT(varbinary,NEWID()))*@Limit,0,1)+1
IF NOT EXISTS (SELECT [Num] FROM #Nums WHERE [Num] = @Number)
BEGIN
INSERT INTO #Nums VALUES(@Number)
SET @Count += 1
END
END

DECLARE @Chamber INT

WHILE 1=1
BEGIN
WAITFOR DELAY '00:00:03'
  SET @Chamber = (SELECT TOP 1 [Num] FROM #Nums)
    IF @Chamber = 1
    BEGIN
      RAISERROR('you die!, Unlucky',10,1) WITH NOWAIT
      BREAK
    END
   RAISERROR('you survive., Phew...',10,1) WITH NOWAIT
  DELETE FROM #Nums WHERE [Num] = @Chamber
END

DROP TABLE #Nums

As Larnu already aluded to in comments, this isn't a good use of T-SQL.

SQL is a set-oriented language. We try not to write procedural code (do this, then do that, then run this block of code multiple times). We try to give the server as much as possible in a single query and let it work out how to process it. Whilst T-SQL does have language support for loops, we try to avoid them if possible.

1I'm using packets very loosely here. Note that it applies the same optimizations no matter what networking (or no-networking-local-memory) option is actually being used to carry the connection between client and server.

Upvotes: 3

Related Questions