Need help regarding performance with a complicated SQL query

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))
)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
The Query is tooooooooo long so breake it into 2 or 3 queries, so lets say you will have 3 temp or 3 tables in CTE format at the same time you must
convert the data type as needed
apply the WHERE clause
prefilter the start and end date
apply the case statement
so now at this point you have 3 tables now you can simply join them together
and please use JOIN clasue

good luck
Manish Kumar8y ago
You have your answer in your question.
Also Why you have used distinct because it will make query slow. Check your table sturcture for duplicates records.
Also share your execution plan, no. of records in all tables for analyzing performance.