Slow query with where clause

Question

I have following sql query that take only 1 second to execute:

select a.date, b.rate, c.type, a.value from

a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx'

But I need a resultset to get the results that has rate greater than 0. So when I change the query to this it takes 7 minutes to execute:

select a.date, b.rate, c.type, a.value from

a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx' and b.rate>0

Why would this make the query time go up from 1 sec to 7 mins? Since the b table is huge I even tried to use CTE but that did not improve the performance either. I thought with the CTE there will be smaller set of values to filter from so it should be faster but that did not help:

;with x as
(select a.date, b.rate, c.type, a.value from

a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx')
select * from x where rate>0

I cannot include the execution plan as I don't have permissions to db other than querying.

Answer 1

My guess is that the slow execution plan is doing the rate>0 filter in an unfortunate way, like as part of a scan on the inside of a loop join or something.

If it comes down to it, one solution would be to store an intermediate result set and filter it in a separate statement.

I suggest this with the understanding that you can't make changes to your vendor's database and that you are basically stuck. This is essentially taking away some control from the optimizer -- something you don't typically want to do -- and is also adding a relatively small amount of overhead with the creation of a temp table. But it should alleviate the slowness in this case. I would continue to work with your vendor on indexing strategies if possible.

select a.date, b.rate, c.type, a.value 
into #t
from a inner join b on a.id = b.aid
c inner join b.id = c.bid
where a.name = 'xxx' 

select * from #t where rate>0