Optimising multiple selects with a WHILE loop

Question

I need to run a larger version of the SQL Server 2008 query below (42 repeats of the SELECT, as apposed to the 4 repeats shown below). It does not seem to me to be very optimised, and an ideal candidate for using a while loop, but after may hours of searching and trying I can't get it to work. Can anyone suggest a more optimised query to accomplish what I need?

Thanks

SELECT 
    t1.Occupied as "1", t2.Occupied as "2", 
    t3.Occupied as "3", t4.Occupied as "4"
FROM 
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 22 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t1,
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 1 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t2,
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 1 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t3,
    (SELECT 
         COUNT(function_id) AS Occupied
     FROM 
         ev_functions 
     WHERE 
         room_id = 1 
         AND DATEADD(HOUR,3,GETDATE()) > from_date_time 
         AND GETDATE() < to_date_time 
         AND function_status_id = 6) t4

Off the top of my head - it looks like you could simplify it to a single query with multiple counts in the select if nothing changes but the room id? — Allan S. Hansen, Aug 31 '16 at 11:41
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Aug 31 '16 at 11:49
Noted marc_s, I will try and use proper joins going forward; I had struggled to find a suitable join condition so did it this way, but will look again. Cheers — AdT, Aug 31 '16 at 12:17
Is it just the room_id that is different for each query? If so, you could use a PIVOT statement. The sample is not very clear, as it is limited and queries 2, 3 and 4 are identical. In general, WHILE loops are slower than a single SQL statement. — Henk Kok, Aug 31 '16 at 13:01
You need to walk away from the idea that a loop is going to help here. Looping is something we do a programming languages, data retrieval is set based and using loops will only make things slower. — Sean Lange, Aug 31 '16 at 13:07
I will stick with the solution given below then. Thank you for your help all — AdT, Aug 31 '16 at 17:47

score 0 · Accepted Answer · answered Aug 31 '16 at 11:44

0

In your case,you are accessing Same table multiple times for a small set of changes

use SUM with CASE to avoid that and also for readability

sum(case when room_id = 1 then 1 else 0 end ) as room1,
sum(case when room_id = 22 then 1 else 0 end )..
.....
and so on
from ev_functions 
where  DATEADD(HOUR,3,GETDATE()) > from_date_time 
and GETDATE() < to_date_time 
and function_status_id = 6

answered Aug 31 '16 at 11:44

TheGameiswar

27,855
8
56
94

Thank you TheGameiswar, that looks a lot more elegant, and only uses 47 lines rather than 300+. I don't know enough about them but I do wonder if the query could be simplified further with a while loop though. Any idea if it can? – AdT Aug 31 '16 at 12:20
Can you share some outline of how you are using while loop ? – TheGameiswar Aug 31 '16 at 13:19
The problem is that I don't know how to use WHILE loops in SQL (for example [link](http://stackoverflow.com/questions/22048583/sql-while-loops) ). From comments above, it seems that if it is possible to do using a loop it would be slower, so I'll stick with the solution you gave as I already have it up and working. Thanks for your help. – AdT Aug 31 '16 at 17:43

Optimising multiple selects with a WHILE loop

1 Answers1