[sql] Is a view faster than a simple query?

Is a

select *  from myView

faster than the query itself to create the view (in order to have the same resultSet):

select * from ([query to create same resultSet as myView])

?

It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query.

There is no practical different and if you read BOL you will find that ever your plain old SQL SELECT * FROM X does take advantage of plan caching etc.

Generally speaking, no. Views are primarily used for convenience and security, and won't (by themselves) produce any speed benefit.

That said, SQL Server 2000 and above do have a feature called Indexed Views that can greatly improve performance, with a few caveats:

1. Not every view can be made into an indexed view; they have to follow a specific set of guidelines, which (among other restrictions) means you can't include common query elements like COUNT, MIN, MAX, or TOP.
2. Indexed views use physical space in the database, just like indexes on a table.

This article describes additional benefits and limitations of indexed views:

You Can…

• The view definition can reference one or more tables in the same database.
• Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
• You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.

You Can’t…

• The view definition can’t reference other views, or tables in other databases.
• It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
• You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
• You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.

There should be some trivial gain in having the execution plan stored, but it will be negligible.

In SQL Server at least, Query plans are stored in the plan cache for both views and ordinary SQL queries, based on query/view parameters. For both, they are dropped from the cache when they have been unused for a long enough period and the space is needed for some other newly submitted query. After which, if the same query is issued, it is recompiled and the plan is put back into the cache. So no, there is no difference, given that you are reusing the same SQL query and the same view with the same frequency.

Obviously, in general, a view, by it's very nature (That someone thought it was to be used often enough to make it into a view) is generally more likely to be "reused" than any arbitrary SQL statement.

It all depends on the situation. MS SQL Indexed views are faster than a normal view or query but indexed views can not be used in a mirrored database invironment (MS SQL).

A view in any kind of a loop will cause serious slowdown because the view is repopulated each time it is called in the loop. Same as a query. In this situation a temporary table using # or @ to hold your data to loop through is faster than a view or a query.

So it all depends on the situation.

There should be some trivial gain in having the execution plan stored, but it will be negligible.

It may be faster if you create a materialized view (with schema binding). Non-materialized views execute just like the regular query.

Against all expectation, views are way slower in some circumstances.

I discovered this recently when I had problems with data which was pulled from Oracle which needed to be massaged into another format. Maybe 20k source rows. A small table. To do this we imported the oracle data as unchanged as I could into a table and then used views to extract data. We had secondary views based on those views. Maybe 3-4 levels of views.

One of the final queries, which extracted maybe 200 rows would take upwards of 45 minutes! That query was based on a cascade of views. Maybe 3-4 levels deep.

I could take each of the views in question, insert its sql into one nested query, and execute it in a couple of seconds.

We even found that we could even write each view into a temp table and query that in place of the view and it was still way faster than simply using nested views.

What was even odder was that performance was fine until we hit some limit of source rows being pulled into the database, performs just dropped off a cliff over the space of a couple of days - a few more source rows was all it took.

So, using queries which pull from views which pull from views is much slower than a nested query - which makes no sense for me.

It all depends on the situation. MS SQL Indexed views are faster than a normal view or query but indexed views can not be used in a mirrored database invironment (MS SQL).

A view in any kind of a loop will cause serious slowdown because the view is repopulated each time it is called in the loop. Same as a query. In this situation a temporary table using # or @ to hold your data to loop through is faster than a view or a query.

So it all depends on the situation.

EDIT: I was wrong, and you should see Marks answer above.

I cannot speak from experience with SQL Server, but for most databases the answer would be no. The only potential benefit that you get, performance wise, from using a view is that it could potentially create some access paths based on the query. But the main reason to use a view is to simplify a query or to standardize a way of accessing some data in a table. Generally speaking, you won't get a performance benefit. I may be wrong, though.

I would come up with a moderately more complicated example and time it yourself to see.

It may be faster if you create a materialized view (with schema binding). Non-materialized views execute just like the regular query.

My understanding is that a while back, a view would be faster because SQL Server could store an execution plan and then just use it instead of trying to figure one out on the fly. I think the performance gains nowadays is probably not as great as it once was, but I would have to guess there would be some marginal improvement to use the view.

The purpose of a view is to use the query over and over again. To that end, SQL Server, Oracle, etc. will typically provide a "cached" or "compiled" version of your view, thus improving its performance. In general, this should perform better than a "simple" query, though if the query is truly very simple, the benefits may be negligible.

Now, if you're doing a complex query, create the view.

I ran across this thread and just wanted to share this post from Brent Ozar as something to consider when using availability groups.

Brent Ozar bug report

The purpose of a view is to use the query over and over again. To that end, SQL Server, Oracle, etc. will typically provide a "cached" or "compiled" version of your view, thus improving its performance. In general, this should perform better than a "simple" query, though if the query is truly very simple, the benefits may be negligible.

Now, if you're doing a complex query, create the view.

Select from a View or from a table will not make too much sense.

Of course if the View does not have unnecessary joins, fields, etc. You can check the execution plan of your queries, joins and indexes used to improve the View performance.

You can even create index on views for faster search requirements. http://technet.microsoft.com/en-us/library/cc917715.aspx

But if you are searching like '%...%' than the sql engine will not benefit from an index on text column. If you can force your users to make searches like '...%' than that will be fast

referred to answer on asp forums : https://forums.asp.net/t/1697933.aspx?Which+is+faster+when+using+SELECT+query+VIEW+or+Table+

Generally speaking, no. Views are primarily used for convenience and security, and won't (by themselves) produce any speed benefit.

That said, SQL Server 2000 and above do have a feature called Indexed Views that can greatly improve performance, with a few caveats:

1. Not every view can be made into an indexed view; they have to follow a specific set of guidelines, which (among other restrictions) means you can't include common query elements like COUNT, MIN, MAX, or TOP.
2. Indexed views use physical space in the database, just like indexes on a table.

This article describes additional benefits and limitations of indexed views:

You Can…

• The view definition can reference one or more tables in the same database.
• Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
• You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.

You Can’t…

• The view definition can’t reference other views, or tables in other databases.
• It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
• You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
• You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.

In SQL Server at least, Query plans are stored in the plan cache for both views and ordinary SQL queries, based on query/view parameters. For both, they are dropped from the cache when they have been unused for a long enough period and the space is needed for some other newly submitted query. After which, if the same query is issued, it is recompiled and the plan is put back into the cache. So no, there is no difference, given that you are reusing the same SQL query and the same view with the same frequency.

Obviously, in general, a view, by it's very nature (That someone thought it was to be used often enough to make it into a view) is generally more likely to be "reused" than any arbitrary SQL statement.

Generally speaking, no. Views are primarily used for convenience and security, and won't (by themselves) produce any speed benefit.

That said, SQL Server 2000 and above do have a feature called Indexed Views that can greatly improve performance, with a few caveats:

1. Not every view can be made into an indexed view; they have to follow a specific set of guidelines, which (among other restrictions) means you can't include common query elements like COUNT, MIN, MAX, or TOP.
2. Indexed views use physical space in the database, just like indexes on a table.

This article describes additional benefits and limitations of indexed views:

You Can…

• The view definition can reference one or more tables in the same database.
• Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
• You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.

You Can’t…

• The view definition can’t reference other views, or tables in other databases.
• It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
• You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
• You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.

Definitely a view is better than a nested query for SQL Server. Without knowing exactly why it is better (until I read Mark Brittingham's post), I had run some tests and experienced almost shocking performance improvements when using a view versus a nested query. After running each version of the query several hundred times in a row, the view version of the query completed in half the time. I'd say that's proof enough for me.

It may be faster if you create a materialized view (with schema binding). Non-materialized views execute just like the regular query.

There is no practical different and if you read BOL you will find that ever your plain old SQL SELECT * FROM X does take advantage of plan caching etc.

Definitely a view is better than a nested query for SQL Server. Without knowing exactly why it is better (until I read Mark Brittingham's post), I had run some tests and experienced almost shocking performance improvements when using a view versus a nested query. After running each version of the query several hundred times in a row, the view version of the query completed in half the time. I'd say that's proof enough for me.

There is no practical different and if you read BOL you will find that ever your plain old SQL SELECT * FROM X does take advantage of plan caching etc.

In my finding, using the view is a little bit faster than a normal query. My stored procedure was taking around 25 minutes (working with a different larger record sets and multiple joins) and after using the view (non-clustered), the performance was just a little bit faster but not significant at all. I had to use some other query optimization techniques/method to make it a dramatic change.

I would expect the two queries to perform identically. A view is nothing more than a stored query definition, there is no caching or storing of data for a view. The optimiser will effectively turn your first query into your second query when you run it.

In my finding, using the view is a little bit faster than a normal query. My stored procedure was taking around 25 minutes (working with a different larger record sets and multiple joins) and after using the view (non-clustered), the performance was just a little bit faster but not significant at all. I had to use some other query optimization techniques/method to make it a dramatic change.

My understanding is that a while back, a view would be faster because SQL Server could store an execution plan and then just use it instead of trying to figure one out on the fly. I think the performance gains nowadays is probably not as great as it once was, but I would have to guess there would be some marginal improvement to use the view.

Generally speaking, no. Views are primarily used for convenience and security, and won't (by themselves) produce any speed benefit.

That said, SQL Server 2000 and above do have a feature called Indexed Views that can greatly improve performance, with a few caveats:

1. Not every view can be made into an indexed view; they have to follow a specific set of guidelines, which (among other restrictions) means you can't include common query elements like COUNT, MIN, MAX, or TOP.
2. Indexed views use physical space in the database, just like indexes on a table.

This article describes additional benefits and limitations of indexed views:

You Can…

• The view definition can reference one or more tables in the same database.
• Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
• You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.

You Can’t…

• The view definition can’t reference other views, or tables in other databases.
• It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
• You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
• You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.

In SQL Server at least, Query plans are stored in the plan cache for both views and ordinary SQL queries, based on query/view parameters. For both, they are dropped from the cache when they have been unused for a long enough period and the space is needed for some other newly submitted query. After which, if the same query is issued, it is recompiled and the plan is put back into the cache. So no, there is no difference, given that you are reusing the same SQL query and the same view with the same frequency.

Obviously, in general, a view, by it's very nature (That someone thought it was to be used often enough to make it into a view) is generally more likely to be "reused" than any arbitrary SQL statement.

Select from a View or from a table will not make too much sense.

Of course if the View does not have unnecessary joins, fields, etc. You can check the execution plan of your queries, joins and indexes used to improve the View performance.

You can even create index on views for faster search requirements. http://technet.microsoft.com/en-us/library/cc917715.aspx

But if you are searching like '%...%' than the sql engine will not benefit from an index on text column. If you can force your users to make searches like '...%' than that will be fast

referred to answer on asp forums : https://forums.asp.net/t/1697933.aspx?Which+is+faster+when+using+SELECT+query+VIEW+or+Table+

I would expect the two queries to perform identically. A view is nothing more than a stored query definition, there is no caching or storing of data for a view. The optimiser will effectively turn your first query into your second query when you run it.

Definitely a view is better than a nested query for SQL Server. Without knowing exactly why it is better (until I read Mark Brittingham's post), I had run some tests and experienced almost shocking performance improvements when using a view versus a nested query. After running each version of the query several hundred times in a row, the view version of the query completed in half the time. I'd say that's proof enough for me.

There should be some trivial gain in having the execution plan stored, but it will be negligible.

My understanding is that a while back, a view would be faster because SQL Server could store an execution plan and then just use it instead of trying to figure one out on the fly. I think the performance gains nowadays is probably not as great as it once was, but I would have to guess there would be some marginal improvement to use the view.

EDIT: I was wrong, and you should see Marks answer above.

I cannot speak from experience with SQL Server, but for most databases the answer would be no. The only potential benefit that you get, performance wise, from using a view is that it could potentially create some access paths based on the query. But the main reason to use a view is to simplify a query or to standardize a way of accessing some data in a table. Generally speaking, you won't get a performance benefit. I may be wrong, though.

I would come up with a moderately more complicated example and time it yourself to see.

I ran across this thread and just wanted to share this post from Brent Ozar as something to consider when using availability groups.

Brent Ozar bug report

My understanding is that a while back, a view would be faster because SQL Server could store an execution plan and then just use it instead of trying to figure one out on the fly. I think the performance gains nowadays is probably not as great as it once was, but I would have to guess there would be some marginal improvement to use the view.

Against all expectation, views are way slower in some circumstances.

I discovered this recently when I had problems with data which was pulled from Oracle which needed to be massaged into another format. Maybe 20k source rows. A small table. To do this we imported the oracle data as unchanged as I could into a table and then used views to extract data. We had secondary views based on those views. Maybe 3-4 levels of views.

One of the final queries, which extracted maybe 200 rows would take upwards of 45 minutes! That query was based on a cascade of views. Maybe 3-4 levels deep.

I could take each of the views in question, insert its sql into one nested query, and execute it in a couple of seconds.

We even found that we could even write each view into a temp table and query that in place of the view and it was still way faster than simply using nested views.

What was even odder was that performance was fine until we hit some limit of source rows being pulled into the database, performs just dropped off a cliff over the space of a couple of days - a few more source rows was all it took.

So, using queries which pull from views which pull from views is much slower than a nested query - which makes no sense for me.

It may be faster if you create a materialized view (with schema binding). Non-materialized views execute just like the regular query.

EDIT: I was wrong, and you should see Marks answer above.

I cannot speak from experience with SQL Server, but for most databases the answer would be no. The only potential benefit that you get, performance wise, from using a view is that it could potentially create some access paths based on the query. But the main reason to use a view is to simplify a query or to standardize a way of accessing some data in a table. Generally speaking, you won't get a performance benefit. I may be wrong, though.

I would come up with a moderately more complicated example and time it yourself to see.

The purpose of a view is to use the query over and over again. To that end, SQL Server, Oracle, etc. will typically provide a "cached" or "compiled" version of your view, thus improving its performance. In general, this should perform better than a "simple" query, though if the query is truly very simple, the benefits may be negligible.

Now, if you're doing a complex query, create the view.

I would expect the two queries to perform identically. A view is nothing more than a stored query definition, there is no caching or storing of data for a view. The optimiser will effectively turn your first query into your second query when you run it.

EDIT: I was wrong, and you should see Marks answer above.

I cannot speak from experience with SQL Server, but for most databases the answer would be no. The only potential benefit that you get, performance wise, from using a view is that it could potentially create some access paths based on the query. But the main reason to use a view is to simplify a query or to standardize a way of accessing some data in a table. Generally speaking, you won't get a performance benefit. I may be wrong, though.

I would come up with a moderately more complicated example and time it yourself to see.

There is no practical different and if you read BOL you will find that ever your plain old SQL SELECT * FROM X does take advantage of plan caching etc.

There should be some trivial gain in having the execution plan stored, but it will be negligible.

Definitely a view is better than a nested query for SQL Server. Without knowing exactly why it is better (until I read Mark Brittingham's post), I had run some tests and experienced almost shocking performance improvements when using a view versus a nested query. After running each version of the query several hundred times in a row, the view version of the query completed in half the time. I'd say that's proof enough for me.