June 14, 2016 at 8:29 am
Howdy:
I'm working a on script that contains an OR statement in the where clause. Below is the contents of the clause.
where
(
enc.ADT_PAT_CLASS_C = '103'
and
enc.adt_arrival_time between dateadd(dd,-180,cast(getdate() as date)) and cast(getdate() as date)
and
enc.ed_episode_id is not null
)
or
(
enc.ADT_PAT_CLASS_C = '101'
and
enc.hosp_admsn_time between dateadd(dd,-180,cast(getdate() as date)) and cast(getdate() as date)
)
I've have seen ORs cause a hit to the performance of queries. I was wondering if I used two separate select statements one using the upper portion of the where clause and a second using the lower portion of the where clause with a union statement would decrease the running time of the query. So an example of the query would like this:
select
*
from
blah [enc]
where
enc.ADT_PAT_CLASS_C = '103'
and
enc.adt_arrival_time between dateadd(dd,-180,cast(getdate() as date)) and cast(getdate() as date)
and
enc.ed_episode_id is not null
union
select
*
from
blah [enc]
enc.ADT_PAT_CLASS_C = '101'
and
enc.hosp_admsn_time between dateadd(dd,-180,cast(getdate() as date)) and cast(getdate() as date)
Any thoughts on this would be appreciated.
Thanks,
Ralph
June 14, 2016 at 8:36 am
I'd be tempted to run both, in the same batch, with Include Actual Execution Plan turned on.
Then you'll see each query's relative cost percentage & might be able to identify areas which can be improved.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 14, 2016 at 8:55 am
Most likely, SQL Server will resolve the OR and the Union options the same way: Split the query in two and then merge the results together.
Look at the execution plans for both and see what it does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2016 at 8:58 am
Change your UNION to a UNION ALL, unless you want to eliminate duplicates.
UNION will need to add a sort which is expensive.
If your queries have adequate indexes, I'd go with UNION ALL. If you're doing a table scan (or clustered index scan), you might want to use the OR option.
June 14, 2016 at 9:20 am
Luis Cazares (6/14/2016)
Change your UNION to a UNION ALL, unless you want to eliminate duplicates.UNION will need to add a sort which is expensive.
If your queries have adequate indexes, I'd go with UNION ALL. If you're doing a table scan (or clustered index scan), you might want to use the OR option.
Well spotted.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 14, 2016 at 9:48 am
Luis Cazares (6/14/2016)
Change your UNION to a UNION ALL, unless you want to eliminate duplicates.UNION will need to add a sort which is expensive.
If your queries have adequate indexes, I'd go with UNION ALL. If you're doing a table scan (or clustered index scan), you might want to use the OR option.
Luis beat me to it. It seems at first blush that your WHERE clause is guaranteed to be mutually-exclusive so there cannot possibly be duplicates. That SORT/DISTINCT under the covers can be harsh from a performance standpoint.
I have seen what you are trying to do reap great rewards from a performance standpoint quite a number of times over the last two decades of consulting on SQL Server. Rarely does it make things worse. If the optimizer can get better estimates and then use very efficient plans (i.e. index seeks/nested loop joins on few actual rows) the resulting plan can be multiple orders of magnitude more efficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 14, 2016 at 11:57 am
it also looks to me like you could combine the WHERE statement into a single query, that might be more efficient too:
aww nevermind, i didn't notice the IS NOT NULL in the first, but not the second....
still left the code for my ideal, even though it is WRONG.
where
enc.ADT_PAT_CLASS_C IN( '103','101')
and
enc.adt_arrival_time between dateadd(dd,-180,cast(getdate() as date)) and cast(getdate() as date)
and
enc.ed_episode_id is not null
Lowell
June 14, 2016 at 11:59 am
Lowell (6/14/2016)
it also looks to me like you could combine the WHERE statement into a single query, that might be more efficient too:aww nevermind, i didn't notice the IS NOT NULL in the first, but not the second....
still left the code for my ideal, even though it is WRONG.
where
enc.ADT_PAT_CLASS_C IN( '103','101')
and
enc.adt_arrival_time between dateadd(dd,-180,cast(getdate() as date)) and cast(getdate() as date)
and
enc.ed_episode_id is not null
Also, the date-range depends on the 103 or 101.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply