Join Inside View is Very Slow

Question

If you can think of a better title for my question, feel free to edit my post.

Before I begin, please keep the following in mind:

1. I cannot materialize any of the views

2. I cannot create any indexes

3. All of the columns that are in the select t1.* are being used further on, it just slows at this part for some reason.

I have a view. This view used to complete in 30 seconds.

Now, this view takes at least 2 hours (I gave up and cancelled the query after 2 hours).

Here is my code:

Query 1 that took 30 seconds:

select 
     t1.*,
     t2.legacysystemid as Servicerateid
from 
     NONWAIVER_RATES as t1
left join 
     v_SERVICERATE as t2 on t1.service = t2.service 
                         and t1.fundingsource = t2.fundingsource 
                         and t1.provider = t2.provider 
                         and t1.businessunit = t2.businessunit 
                         and t1.startdate = t2.startdate 
                         and t1.enddate = t2.enddate    
where 
     t2.service is not null

Query 2 that took longer than 2 hours:

Exactly the same as query 1, except t2.provider and t2.businessunit is NULL based on certain criteria.

What could I do to speed up this join? Does joining on NULL values slow down performance?

Edit:

I figured it out, in the v_servicerate view, I am using a case statement.

        case
           when lu_service.[iswaivereligible] = 0 AND lu_service.[isvariablerate] = 0 
              then NULL
              else t1.[fprovider]
        end as [provider]

--------------------------------------------

        case
           when lu_service.[iswaivereligible] = 0 AND lu_service.[isvariablerate] = 0 
              then NULL
              else t1.[businessunit]
        end as [businessunit]

-------------------------------------------------------------------------

        case
           when lu_service.[iswaivereligible] = 0 AND lu_service.[isvariablerate] = 0 
              then NULL
              else t1.[providerid_parent]
        end as [providerid_parent]

---------------------------------------------------

        case
           when lu_service.[iswaivereligible] = 0 AND lu_service.[isvariablerate] = 0 
              then NULL
              else t1.[providerid_child]
        end as [providerid_child]
-----------------------------------------------------------------

If I remove the case statements and just use the columns themselves (aka [providerid_parent], [providerid_child], [provider], and [businessunit])

Everything works super fast again.

Now I just need to figure out how I can have my cake and eat it too . . .

Answer 1

The problem had to do with my case statement:

I had a case statement in some of my columns that was a bit selective (see my original post). I also had a similar case statement as my join criteria.

How I solved the problem was by doing using the following format:

with everything as (

select *
from table
where criteria is not matched

union all

select *
from same_table_as_above
where criteria is matched

)

...rest of my query . . .

The big picture idea as to what I was doing was to separate records that matched certain criteria and records that didn't match certain criteria and then union them together.

By doing it this way, I was able to go back to having a 30 second query, while getting the results I wanted.

In other words, I got my cake and ate it too.

Problem is now solved.

Edit:

By doing it this way, I changed the execution plan. I am not an expert on execution plans, but I know that is why my change was faster.

Answer 2

for the case statements you might check and see if iif() is faster since they only use one case when else per, example

,case
            when lu_service.[iswaivereligible] = 0 AND lu_service.[isvariablerate] = 0 then NULL
            else t1.[fprovider]
            end as [provider]

-- becomes


,iif(lu_service.[iswaivereligible] = 0 AND lu_service.[isvariablerate] = 0,null,t1.[fprovider]) [provider]

the time difference I found with 10mil records, minimal benefit.

/* case statement for 10 million records */
SQL Server Execution Times: CPU time = 3407 ms, elapsed time = 3404 ms.

/* instant if for the same 10 million records */
SQL Server Execution Times: CPU time = 3297 ms, elapsed time = 3366 ms.

first thoughts on the view..some of it is meaningless now that you found your case statement issues :) but here it is

--first, updatestats then check execution time 
exec sp_updatestats;
GO

-- next, I'd set "with schemabinding" and see if it's faster
create view blah with schemabinding
as
select 
     t1.*,
     t2.legacysystemid as Servicerateid
from 
     NONWAIVER_RATES as t1
left join 
     v_SERVICERATE as t2 
     on 
     t1.service=t2.service 
     and t1.fundingsource=t2.fundingsource 
     and t1.provider=t2.provider 
     and t1.businessunit=t2.businessunit 
     and t1.startdate=t2.startdate 
     and t1.enddate=t2.enddate    

where 
     t2.service is not null
GO

-- next I'd rework the join a bit, trying, not likely to work. sql should do this or something better "behind the scenes"

create view blah 
as
     t1.*,
     t2a.Servicerateid
from 
     NONWAIVER_RATES as t1
left join 
(select
      t2.fundingsource 
     ,t2.provider 
     ,t2.businessunit 
     ,t2.startdate 
     ,t2.enddate  
     ,t2.legacysystemid as Servicerateid
from 
     NONWAIVER_RATES as t1
inner join 
     v_SERVICERATE as t2 
     on 
     t1.service=t2.service -- if service is null it cannot match any value, even null.
     and t1.fundingsource=t2.fundingsource 
     and t1.provider=t2.provider 
     and t1.businessunit=t2.businessunit 
     and t1.startdate=t2.startdate 
     and t1.enddate=t2.enddate   
where 
     t2.service is not null   ) t2a 
   on 
     t1.service=t2a.service 
     and t1.fundingsource=t2a.fundingsource 
     and t1.provider=t2a.provider 
     and t1.businessunit=t2a.businessunit 
     and t1.startdate=t2a.startdate 
     and t1.enddate=t2a.enddate 

GO