CODEWITHSUNDEEP
performancesql-server-2005view
jmpena
jmpena

Reputation: 1419

Difference Between Views and Tables in Performance

What is best for tables with a lot of data?

I have a stored procedure that creates a report based on some filters. In my SP I read the table and put all the inner joins and formulas then in the where condition I put the filters.

Talking about performance what's better?

Create a view with all the joins OR read the table (as I'm doing)?

Upvotes: 21

Views: 23863

Answers (3)

Nguyen Thanh Tung
Nguyen Thanh Tung

Reputation: 21

When you use select count(*) from view it will significantly slow than table. Because the table contains row number on its header, a view doesn't have such information.

Upvotes: 2

Otávio Décio
Otávio Décio

Reputation: 74290

Performance is a lot more dependent on having the appropriate indexes than if you are using a view or direct table access, which (except for materialized views) behave exactly the same way.

Upvotes: 23

Mitch Wheat
Mitch Wheat

Reputation: 300699

It depends.

As long as the View does not contain aggregations (or constructs that require materialisation 'upfront'), it will be exactly the same performance (and in many cases can pass through where criteria with shortcircuiting by the optimiser)

Have you tried benchmarking in your specific cases?

@Otávio Décio beat me to it, by mentioning that having the 'correct' indexes will have a greater effect on performance.

Upvotes: 6

Related Questions