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.