I guess my intitial response would be...
1. Why are you doing a sort in a view? In other words, why is the sort required and what/where will the result set be used for? Sorts in a view are generally considered to be a "worst practice" although, like everything else, there are exceptions.
2. Do you know how to read the "Actual Execution Plan"? And, it's ok to say "No".
3. How often does the data in the underlying tables change?
4. I could be wrong but it seems obvious that you're having performance issues. Is it possible to comply with the article at the second link in my signature line below?
As to your general question, a view will use the indexes of the underlying tables just as if you had written a stored procedure or even just a script with the same query in it. If the indexes aren't being used correctly for the script version of the query, then the indexes won't be used correctly for the View. Using an indexed view to materialize the data may be one workaround but that has some serious implications for tables that suffer a good number of inserts or updates. Generally speaking (there are, of course, exceptions but they're more rare than not), the creation of an indexed view for performance is a poor substitute for correct tuning because, ironically, the indexed view will perform better if its code is tuned and the tuned (or just properly written) code may totally negate the need for an indexed view.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs