August 10, 2003 at 6:54 am
We had a simple view that was running obscenely slowly, and I think I've tracked down the issue.
There are two relatively large tables involved (a few hundred million rows each). They are joined over a 4 part composite key, fully indexed. When queried based on portions of indexed fields (e.g. 3 of the 4, including a left subset), the view was not using the index, it was doing a table scan on both.
E.g.
create view x
as
select t1.a, t1.b, t1.c, t1.d, t1.stff, t2.stuff
from t1
full join t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d
go
select * from x where a=1 and b=2 and c=3
As shown above, I always get table scans. But if I make it an inner join, it very intelligently decides to use the index (by intelligently I mean it looks at the where clause, decides an estimated cardinality, and makes good decisions on scan vs. index).
Any suggestions?
August 10, 2003 at 8:31 am
Never mind. I oversimplified, and once I noticed it's obvious. The view is actually
select IsNull(t1.a,t2.a) as a, etc.
so the where clause for the view isn't making it back through the IsNull where the optimizer knows it can deal with it.
August 11, 2003 at 4:57 pm
FULL JOIN = Table Scan because all rows meet the criteria....unless the composite key includes all the values returned in the select statement. In which case, the you would have an index scan instead of a table scan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply