1

I have a table (Not all columns shown) that shows type. Typeid is the PK. There is some date overlap that should not be there. I want to remove those from my query.

Custid    typeid      start_dt     end_dt
 101        352       3/28/2017    1/16/2019
 101        353       1/15/2018    1/15/2019

Data shows the below (example of overlap). 

Custid    typeid      start_dt     end_dt     overlap
 101        352       3/28/2017    1/16/2019    1
 101        353       1/15/2018    1/15/2019    1
 201        426       1/1/2019     12/31/9999   0

SQL. (Index are on all of the above table columns)

select *
from (select
      Custid, 
      typeid,
      start_dt,
      case
      when end_dt   > lead (start_dt) over (partition by custid order by start_dt) then 1
      when start_dt < lag (end_dt) over (partition by custid order by start_dt)    then 1
      else 0
      end overlap)
   where overlap = 0

The filtering on the case expression slows down the query returning results. Is there any tips/tricks to improve the query performance?

Mr John
  • 231
  • 1
  • 3
  • 18
  • I'm not sure how to interpret "Index are on all of the above table columns". Is there one index that covers all of the columns? Or a separate index for each individual column? Or something else? – Dave Costa Dec 03 '19 at 19:16
  • @DaveCosta Separate indexes for each column – Mr John Dec 03 '19 at 19:17

1 Answers1

0

It may help if you can show the execution plan, and more specifics on the existing indexes.

My immediate thought is that an index on (custid, start_dt) might avoid the need to sort to implement the windowing logic.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72