TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Hakan Axheim
NA
201
32.9k
Need help regarding performance with a complicated SQL query
Mar 26 2018 3:20 PM
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))
)
Reply
Answers (
2
)
Transact-SQL(PARSE,TRY_PARSE,TRY_CONVERT)
Naming convention of Table and Fields name.