SQL View performance vs. SELECT

5 pts.
Tags:
SELECT statement
SQL Server performance
SQL Server views
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 clarify this for me. Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dmenke38
    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.
    185 pointsBadges:
    report
  • Koverton44
    That would also be true of a table query.
    150 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following