Need help regarding performance with a complicated SQL query

Question

Hi,

 

I need help with a complicated SQL query regarding to performance.

 

This SQL query executes in an SQLServer 2015 relational database server in a stored procedure.

One difficulty is that room_period.beginning and room_period.beginTime does not have the same datatype. Beginning is an Datetime and beginTime is CHAR(5). This is very stupid because when I concatenate this 2 columns with + it will be an implicit conversion which turns off index.

 

As you see there is a lot of logic in the select statement with case when and so on.

 

There is also a lot of logic in the where clause. We also use select distinct in this query.

 

Should we be careful with the select distinct statement?

 

Do the select distinct statement generate a table scan?

 

Regarding to performance is it better to create a cursor in the stored procedure with a SQL query that has no case when logic. All such logic will then be performed in a WHILE statement.

 

How to get rid of the implicit conversion which turns off index?

 

Unfortunately, I cannot change any datatype in the database.

 

Here comes the SQL query:

select distinct room_status.col1, room_status.col2, room_status.col3, room_status.col4, room_status.col5,
-- start
case when
room_period.beginning is not null and room_period.begintime is not null
then
(room_period.beginning + room_period.begintime)
else
null
end as 'startTime',
-- end
case when
room_period.endDate is not null and room_period.endTime is not null
then
(room_period.endDate + room_period.endTime)
when
room_period.endDate is null and room_period.probable_end is not null and room_period.probable_end <= @END
then
room_period.probable_end
else
null
end as 'endTime',
case
--- busy-unavailable
when
(room_status.col4='inactive' or room_status.col4='suspended')
or not (
(@START >= rooms.robeginning)
and
(rooms.roend is null or @END <= rooms.roend)
)
then 'busy-unavailable'
--- busy
when (
(
(room_period.endDate is not null and room_period.endTime is not null and (room_period.endDate + room_period.endTime) >= @START )
or
(room_period.probable_end is not null and room_period.probable_end >= @START)
or
((room_period.beginning + room_period.begintime) >= @START and (room_period.beginning + room_period.begintime) <= @END )
or
((room_period.endDate is null or room_period.endTime is null) and (room_period.beginning + room_period.begintime) <= @END)
or
(room_period.probable_end is null and (room_period.beginning + room_period.begintime) <= @END)
)
and
room_status.col4 = 'active'
)
then 'busy'
else 'unknown' end as freeBusy,
room_period.person_id,
room_period.probable_end
from table1 as rooms, table2 as room_period, table3 as room_status
where rooms.roidnr=room_status.col2
and rooms.roward=room_status.col1
and room_period.wpward = room_status.col1
and room_period.wproom = room_status.col2
and room_period.wpbed = room_status.col3
and room_period.wpstatus = '1'
and (
((room_period.endDate is null or room_period.endTime is null) and @END > (room_period.beginning + room_period.begintime))
or
((room_period.endDate is not null and room_period.endTime is not null) and @START < (room_period.endDate + room_period.endTime) and @END > (room_period.beginning + room_period.begintime))
or
((room_period.endDate is null or room_period.endTime is null) and room_period.probable_end is not null and @START <= room_period.probable_end and @END >= (room_period.beginning + room_period.begintime))
or
((room_period.endDate is not null and room_period.endTime is not null) and room_period.probable_end is not null
and @START < (room_period.endDate + room_period.endTime) and @START <= room_period.probable_end and @END >= (room_period.beginning + room_period.begintime))
)