June 3, 2003 at 11:59 pm
Greetings all.
I rewrote a proc which selected from a view. This view also selected from a view.
I prefer not to use views (personal pref.)
After removing the view from the proc and using joins (Exactly what the view did) the proc ran faster (Couple of hundrend milliseconds).
In another proc, using the view was faster.
My quoestion is are views quicker than joins?
We are not using SQL 2K Enterprise so indexes (Assuming I had them on the view) would not be used be default.
When selecting from a view, do the underlying indexes for the tables get used or would it be a "table scan" on the view's records?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 4, 2003 at 4:34 am
Generally it depedns on what is being done in the views as to whether or not is faster. Yes the undelying indexes are used (use Execution Plan view to see for yourself). Generally thou I avoid view in SPs unless I will reuse the same one each time and it only returns exactly what I need without a lot of extra joining or data thru other views.
June 4, 2003 at 7:14 am
I can understand that when the view is executed it would use the indexes.
In the proc, there are plenty where clauses that referr to the columns returned by the view. Would thos use the underlying indexes?
If so, what is the advantage of indexing a view?
Example:
Select * from vw_xxx
Where
xx = 1
and yy = 2
and tt = 3
etc etc.
Would it use the index from the table with xx column?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply