CODEWITHSUNDEEP
sql-serverperformanceview
franz976
franz976

Reputation: 333

SQL Server Views performances

If I have defined a view in SQL Server like this:

CREATE View V1 
AS
SELECT *    
FROM t1
INNER JOIN t2 ON t1.f1 = t2.f2    
ORDER BY t1.f1

Should I expect performance differences between

SELECT * FROM V1 WHERE V1.f1 = 100

and just avoiding view, like this

SELECT *    
FROM t1
INNER JOIN t2 ON t1.f1 = t2.f2    
WHERE t1.f1 = 100
ORDER BY t1.f1

?

We don't have any reason to use views except the need to centralize complex queries.

Thanks

Upvotes: 1

Views: 177

Answers (2)

Martin Smith
Martin Smith

Reputation: 453348

Generally you shouldn't expect performance differences but check the execution plans for your queries.

If you are joining Views onto Views then the execution plans can be sub optimal and contain repeated accesses to the same table that could have been consolidated. Also there can be issues with views and predicate pushing.

Upvotes: 0

Oded
Oded

Reputation: 499062

There should be no performance penalty.

Simplifying complex queries is what views are for.

If performance is something you are concerned about - read about indexed views in SQL Server:

indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.

Upvotes: 5

Related Questions