Fast query for end user report

Question

In project users can reserve rooms for events. In one event we can have many reservations (tblEventTimePeriod) with many rooms (tblEventTimePeriodRoom) I have DB structure like this. I removed unnecessary columns to simplify the example,

tblEvent (ID, Name) 
tblEventTimePeriod (ID, EventId)
tblEventTimePeriodRoom (ID, EventTimePeriodId, RoomId)

Relationships between tables:

tblEvent to tblEventTimePeriod -> One to many
tblEventTimePeriod to tblEventTimePeriodRoom -> many to many

For this example, RoomId can take values from 1 to 5. In real project it has 40 different values (Keys in other table) which I have to show as columns in report.

My problem is - how to build fast query to get result as below:

EventId | EventName | RoomId_1 | RoomId_2 | RoomId_3 | RoomId_4 | RoomId_5

RoomId_X - meens than Event has reserved RoomId = X. It doesn't matters which tblEventTimePeriod has this reservation.

Actual solution is using scalar UDF (User Defined Function) to get this information. At the beginning it was fine, but now executation time is not acceptable. In fact, For each row (tblEvent) it execute subquery to tblEventTimePeriodRoom joined to tblEventTimePeriod to check row exists. When report has 40 columns.... No comment :)

I will be grateful for any hints! I'm using SQL Server 2008 R2.

Example data:

tblEvent:
----------
Id | Name
----------
1 | Event1
2 | Event2
3 | Event3

tblEventTimePeriod:
------------
Id | EventId
------------
12 | 1
13 | 2
14 | 2
15 | 3

tblEventTimePeriodRoom
-------------------------------
Id | EventTimePeriodId | RoomId
-------------------------------
110 | 15 | 1
111 | 15 | 5
112 | 13 | 5
113 | 14 | 2
114 | 14 | 3
115 | 14 | 4
116 | 14 | 5
117 | 12 | 1

Result shoud be:
--------------------------------------------------------------------------
EventId | EventName | RoomId_1 | RoomId_2 | RoomId_3 | RoomId_4 | RoomId_5
--------------------------------------------------------------------------
1 | Event1 | 1 | 0 | 0 | 0 | 0
2 | Event2 | 0 | 1 | 1 | 1 | 1
3 | Event3 | 0 | 0 | 0 | 0 | 1

Best regards!

Can you do the transform in the application code rather than in sql? — 000, Jun 12 '13 at 16:35
Have you tried selecting the data by joining each event to all rooms, setting an indicator if the room is booked or not, and then using the PIVOT function? http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx — liebs19, Jun 12 '13 at 17:11
@JoeFrambach Report mechanism is using views / table valued functions for loading data, so I try to change actual view. If I don't find interesting solution I will load initial data from view (or views if needed) and than make transform as you said. Thanks. — robertw, Jun 12 '13 at 20:39
@liebs19 No I didn't try this solution. Could you send example using data frpm my post? I'm asking because I don't know how to mark room as booked. Thanks. — robertw, Jun 12 '13 at 20:47
Join events to a list of all available rooms. From here, you can left join to the reservation to see if there is one of not. Putting an `isnull()` around this will let you pt a 0 if there isn't one. See this example for a dynamic way to generate columns for the pivot. http://stackoverflow.com/questions/7822004/pivots-with-dynamic-columns-in-sql-server — liebs19, Jun 12 '13 at 22:31

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

Try this:

 /* outer query formats results */
 select EventID, EventName,
 case when RoomID = 1 then 1 else 0 end as Room1,
 case when RoomID = 2 then 1 else 0 end as Room2,
 case when RoomID = 3 then 1 else 0 end as Room3,
 case when RoomID = 4 then 1 else 0 end as Room4,
 case when RoomID = 4 then 1 else 0 end as Room5
 from (
          /* inner query makes the joins */
          select  b.eventid as EventID, a.name as EventName, c.roomid as RoomID
          from _event a inner join _eventTimePeriod b
          on a.id = b.eventid 
          inner join _eventTimePeriodRoom c 
          on c.eventtimeperiod = b.id
) v
order by EventID

I hope this helps you ..

SQL Fiddle Example

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 13 '13 at 07:57

Brian

3,653
1
22
33

That was the first time I used that SQL Fiddle ... nice little web app. – Brian Jun 13 '13 at 08:36
@briandines I think this is what I was looking for :) Of course I need group by to have only one row per event. Thanks brian! I will let you know when I check it in real life :) – robertw Jun 13 '13 at 09:56
@Mr47 I need use MAX instead SUM, one room can be booked in many reservations in the same event. – robertw Jun 13 '13 at 10:07
Sounds reasonable ... glad to help – Brian Jun 13 '13 at 10:37
@briandines I've done test. Query execute time is shorter than oryginal, but only about 10-12% ;/ So this solution is not this what I was looking for. Finally I've solved problem using two queries. First select all needed information about events, second get all reservations for all events. Setting flag (booked or not) is done in application code. Orginal time ~ 1 min, after change: 5 sec :) Thanks for all! – robertw Jun 13 '13 at 11:58
Another option for pure speed is to create a denormalized table where all the data is effectively in one location and you query only on the primary key. So you'd keep your regular tables as they are for inserts, updates, deletes, but you'd create a separate table that collects and holds all the information in one location, allowing you to skip the join and pull everyting you need based on the primary key. But these reporting tables are tricky to maintain. – Brian Jun 13 '13 at 12:04
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/31717/discussion-between-robertw-and-brian-dines) – robertw Jun 13 '13 at 12:05
I'll add I'm surprised you're having speed problems from a query like the one I gave you. SQL is optimized for that kind of thing. Make sure you have primary key indexes on each of the ID fields you're joining on. – Brian Jun 13 '13 at 12:06

Fast query for end user report

1 Answers1