0

I am using SQL Server 2016 (localDB). I have the following DB script which takes 8 seconds, however, the select statement only take 0 seconds if I run separately. Actually that select statement always return 0 rows.

Do you know why it is slow in this case?

PS: I have another similar statement, which works fine, only this one takes super long.

while exists (select *
              from vidyoRoomCall vrc
              inner join reportEffectiveRoomUsage eu on vrc.vidyoRoomId = eu.vidyoRoomId
                                                     and vrc.leaveTime is not null
                                                     and datediff(second, vrc.joinTime, vrc.leaveTime) < 100000 -- @max_second
              where (vrc.joinTime between eu.[start] and eu.[end] 
                     and vrc.leaveTime > eu.[end])
                 or (vrc.leaveTime between eu.[start] and eu.[end] 
                     and vrc.joinTime < eu.[start])
             )
begin
    print 'ignore this since it is only run here' -- this never print
end
Xin
  • 33,823
  • 14
  • 84
  • 85
  • did you see any prints for those 8 seconds? – Anton Jan 03 '18 at 05:26
  • Looping always will be slow. As SQL developed base SET theory the sql server engine will work more efficient with Joins than procedural approach like looping. So Select taking lesser time where while is looping all those records – Shakeer Mirza Jan 03 '18 at 05:42
  • Are you trying to grab a row at a time by any chance? – P.Salmon Jan 03 '18 at 07:12
  • 1
    IF you are asking why it is slower when you wrap it in `exists` this may be because you get different plans due to the [row goal](http://sqlblog.com/blogs/paul_white/archive/2010/08/18/inside-the-optimiser-row-goals-in-depth.aspx). What do the execution plans look like? – Martin Smith Jan 03 '18 at 07:34
  • Please confirm that `PRINT` never returns anything, as it may be a case where [print results are cached](https://stackoverflow.com/questions/306945/how-do-i-flush-the-print-buffer-in-tsql) – Alex Jan 03 '18 at 08:43
  • Hi Yes the `PRINT` never runs. – Xin Jan 04 '18 at 06:06
  • The only problem is why the `select` statement runs slow within `while` loop, and the `select` statement return 0 records – Xin Jan 04 '18 at 06:07

1 Answers1

0

It keeps running until the inner code is true... So probably for a period of 8 seconds, the query returned some results. At the end of 8 seconds, last SELECT did not return any rows which ended the WHILE loop. This is the reason it took 8 seconds. Probably, your code executed hundreds of inner query which returned some rows everytime. So there is no performance difference. Only WHILE loop ...

Eralper
  • 6,461
  • 2
  • 21
  • 27
  • I've updated the question, actually the select statement return always `0` records. So, the while statement never enters. – Xin Jan 03 '18 at 09:24