Is Querying on Views Slower Than Doing One Query

Is querying over a view slower than executing SQL directly?

From MSDN:
View resolution

When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

There should not be any different performance. Views helps you organize, not any performance enhancement. Unless you are using indexed views.

Only the definition of a nonindexed view is stored, not the rows of the view. The query optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.

Is querying on views slower than doing one query?

Whilst in your simple example things will be the same some caution is necessary with using nested views.

I worked on a system where queries were timing out after 30 seconds built on about 6 levels of nested views and managed to speed these up by a factor of about 100 by rewriting the queries against the base tables.

A simple example of the type of issue that can arise is below.

CREATE VIEW MaxTypes
AS
SELECT
  [number],
  MAX(type) AS MaxType
  FROM [master].[dbo].[spt_values]
GROUP BY [number]

GO

CREATE VIEW MinTypes
AS
SELECT
  [number],
  MIN(type) AS MinType
  FROM [master].[dbo].[spt_values]
GROUP BY [number]

GO
SET STATISTICS IO ON

SELECT     MaxTypes.number, MinTypes.MinType, MaxTypes.MaxType
FROM         MinTypes INNER JOIN
                      MaxTypes ON MinTypes.number = MaxTypes.number
ORDER BY MaxTypes.number

/*
Gives

Table 'spt_values'. Scan count 2, logical reads 16, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
GO

SELECT 
  [number],
  MAX(type) AS MaxType,
  MIN(type) AS MinType
  FROM [master].[dbo].[spt_values]
GROUP BY [number]
ORDER BY  [number]

/*
Gives

Table 'spt_values'. Scan count 1, logical reads 8, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/

Sample Image

Is a view faster than a simple query?

Yes, views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries.

Microsoft's own documentation makes it very clear that Views can improve performance.

First, most views that people create are simple views and do not use this feature, and are therefore no different to querying the base tables directly. Simple views are expanded in place and so do not directly contribute to performance improvements - that much is true. However, indexed views can dramatically improve performance.

Let me go directly to the documentation:

After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

Second, these indexed views can work even when they are not directly referenced by another query as the optimizer will use them in place of a table reference when appropriate.

Again, the documentation:

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

This documentation, as well as charts demonstrating performance improvements, can be found here.

Update 2: the answer has been criticized on the basis that it is the "index" that provides the performance advantage, not the "View." However, this is easily refuted.

Let us say that we are a software company in a small country; I'll use Lithuania as an example. We sell software worldwide and keep our records in a SQL Server database. We're very successful and so, in a few years, we have 1,000,000+ records. However, we often need to report sales for tax purposes and we find that we've only sold 100 copies of our software in our home country. By creating an indexed view of just the Lithuanian records, we get to keep the records we need in an indexed cache as described in the MS documentation. When we run our reports for Lithuanian sales in 2008, our query will search through an index with a depth of just 7 (Log2(100) with some unused leaves). If we were to do the same without the VIEW and just relying on an index into the table, we'd have to traverse an index tree with a search depth of 21!

Clearly, the View itself would provide us with a performance advantage (3x) over the simple use of the index alone. I've tried to use a real-world example but you'll note that a simple list of Lithuanian sales would give us an even greater advantage.

Note that I'm just using a straight b-tree for my example. While I'm fairly certain that SQL Server uses some variant of a b-tree, I don't know the details. Nonetheless, the point holds.

Update 3: The question has come up about whether an Indexed View just uses an index placed on the underlying table. That is, to paraphrase: "an indexed view is just the equivalent of a standard index and it offers nothing new or unique to a view." If this was true, of course, then the above analysis would be incorrect! Let me provide a quote from the Microsoft documentation that demonstrate why I think this criticism is not valid or true:

Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes.

Together with the above quote regarding the persistence of data in physical storage and other information in the documentation about how indices are created on Views, I think it is safe to say that an Indexed View is not just a cached SQL Select that happens to use an index defined on the main table. Thus, I continue to stand by this answer.

SQL Server : view MUCH slower than same query by itself

Your query plan is no longer visible, but if you look in the plan, you will most likely see a triangle complaining about the cardinality estimation and/or implicite declaration. What it means is that you are joining tables in a way where your keys are hard to guess for the SQL engine.

It is instant when you run from a query directly, probably because it doesn't need to guess the size of your key is

For example:

k.Id = 970435

SQLSERVER already knows that it is looking for 970435 a 6 digit number.
It can eliminate all the key that doesn't start by 9 and doesn't have 6 digits. :)

However, in a view, it has to build the plan in a way to account for unknown. Because it doesn't know what kind of key it may hold.

See the microsoft for various example and scenario that may help you.

https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver15

If you are always looking for an int, one work around is to force the type with a cast or convert clause. It's may cause performance penalty depending on your data, but it is a trick in the toolbox to tell sql to not attempt the query a plan as varchar(max) or something along that line.

SELECT * 
FROM  [Front].[vw_Details] k
WHERE TRY_CONVERT(INT,k.Id) = 970435

Sql View with WHERE clause runs slower than a raw query

Why this happens? Does the view retrieve all records and then apply Where clause?

Yes, in this particular case, SQL Server will first execute the original underlying query, and then apply a WHERE filter on top of that intermediate result.

Is there a way to speed up the second query? (without applying indexes)

A SQL view generally performs as well as the underlying query. So, if Barcode is a good way to filter off many records, then adding an index to Barcode is the way to go. Other than this, there is not much you can do to speed up the view.

One option would be to create a materialized view, which is basically just a table whose data is generated by your view's query. Selecting all records from a materialized view, with no additional restrictions, should have a speed limited only by the time of data transfer.

Query fast, but when in a VIEW, it's slow - due to ROW_NUMBER

1) Here ROW_NUMBER applies to filtered data only:

SELECT ROW_NUMBER(), ... FROM MyTables WHERE PersonID = x

At first it filters by PersonID, then it computes ROW_NUMBER

2) Here ROW_NUMBER applies to all of the data:

CREATE VIEW MyView as
  select ROW_NUMBER(), ... FROM MyTables

SELECT * FROM MyView WHERE PersonID = x

and only after proceeding full data the filter by PersonID is applied

it's the same as

SELECT * FROM
(SELECT ROW_NUMBER(), ... FROM MyTables
) t
WHERE t.PersonID = x

check out the example:

GO
CREATE VIEW dbo.test_view
AS
    SELECT ROW_NUMBER() OVER (ORDER BY NAME) rn, o.name, o.[object_id]
    FROM sys.objects o
GO
SET SHOWPLAN_XML ON
GO
SELECT rn, o.name, o.[object_id] FROM dbo.test_view o
WHERE OBJECT_ID < 100
GO
SELECT ROW_NUMBER() OVER (ORDER BY NAME) rn, o.name, o.[object_id] FROM sys.objects o
WHERE OBJECT_ID < 100
GO
SET SHOWPLAN_XML OFF
GO
DROP VIEW dbo.test_view
GO

With the view filter operation is in the very end. So plans are different actually.

Query on View is running slower than direct query - in Oracle

The execution plan on each statement will give you more detail on what is happening. Try using some of the provided oracle tools for investigating what exactly is happening in each case.

Try doing a:

 SELECT/*+gather_plan_statistics*/ * FROM TABLE(get_data(12345, 'MYTAB'));

then do a:

 SELECT/*+gather_plan_statistics*/ * FROM my_view

These will give you the actual execution plan for the statements.

By the way, you will need select on the V_$SQL_PLAN and V_$SQL views to use the gather_plan_statistics as above.

Performance of MySQL View is Worse than that of Underlying Query

It's hard to be precise - the best way to investigate is to run EXPLAIN on both flavours of the query.

However, my suspicion is that the query cache is at the heart of this - re-running the same query will seed the query cache, and unless the underlying data changes, or the cache gets flushed, you're likely to benefit from the caching.

You'd expect to get that benefit when hitting the view, too, but caching is non-deterministic, and my guess is that, somehow, your queries against the view aren't benefiting from the caching.

If the EXPLAINs are identical, that would be the best explanation...