SQL View performance vs. SELECT
I am experiencing slow views on my database. If I create a view and run it, then use the same select statement from the view and run. They both return the same data, but View runs slower.
My expectations was that since views are compiled, they should run faster. Could some one clearify this for me.
Thanks
Software/Hardware used:
Software/Hardware used:
ASKED:
March 14, 2009 7:22 AM
UPDATED:
June 8, 2010 1:39 PM
Answer Wiki:
A view is not compiled. Its a virtual table made up of other tables. When you create it doesn't reside somewhere on your server. The underlying queries that make up the view are subject to the same performance gains or dings of the query optimizer. I've never tested performance on a view VS its underlying query, but i would imagine the performance may vary slightly. You can get better performance on an indexed view if the data is relativley unchanging. This may be what you are thinking maybe in terms of "compiled". Search indexed views in BOL.
To see all answers submitted to the Answer Wiki: View Answer History.
A view that is derived from a table that is updated frequently will re-execute the underlying queries nearly every time it is accessed. The view actually creates a temporary result table in the database that is cached until an underlying query is stale (a table specified in the query is updated.)
If no changes occur, the cached table is returned directly, otherwise the underlying queries is re-executed, a slower process.
That would also be true of a table query.
You could try using an Indexed View. There is a good simple overview of these at:
http://www.sqlteam.com/article/indexed-views-in-sql-server-2000
Basically, if your view doesn’t violate any of the limitations of an indexed view, this allows creating a view that can have physical indexes applied to it, so you can tune performance much better than with a non-indexed view.
(Reposting to try to fix bad link ref)
You could try using an Indexed View. There is a good simple overview of these at:
Intro to Indexed Views
Basically, if your view doesn’t violate any of the limitations of an indexed view, this allows creating a view that can have physical indexes applied to it, so you can tune performance much better than with a non-indexed view.