Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Difference Between Views and Tables in Performance

Tags:

performance

sql-server-2005

views

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)?

like image 931
jmpena Avatar asked Jan 02 '11 00:01

jmpena

People also ask

What is the difference between table and views?

A view is a database object that allows generating a logical subset of data from one or more tables. A table is a database object or an entity that stores the data of a database.

Which is faster table or view?

there is no difference. A view is just a stored query which can be referred to in sql queries as though they are tables. Note that this does not apply to materialized views. A view is only a query stored in the data dictionary: it is not going to make your query run faster or slower.

Why do we use views instead of tables?

Views can provide advantages over tables: Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

Are database views slower than tables?

The falsehood is that Views are slower because the database has to calculate them BEFORE they are used to join to other tables and BEFORE the where clauses are applied. If there are a lot of tables in the View, then this process slows everything down.

2 Answers

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.

like image 154
Otávio Décio Avatar answered Nov 16 '22 02:11

Otávio Décio

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.

like image 34
Mitch Wheat Avatar answered Nov 16 '22 04:11

Mitch Wheat
Sign in to Comment

Related questions

How can assigning a variable result in a serious performance drop while the execution order is (nearly) untouched?
Quickest ways to speed up your YII application?
Neo4j Cypher, START n=node(id) over MATCH id(n)=id
Python: Finding random k-subset partition for a given list
Simple Suggestion / Recommendation Algorithm
Speeding up xpath
C++ Function pointers vs Switch
Limiting java application's memory and cpu usage
Which is better: letting Java do autoboxing or using valueOf()
Fastest way to check if a string can be parsed
Haswell memory access
Scala Collection sorted, sortWith and sortBy Performance
Simplify process with linq query
Is there a maximum size that web pages should be kept under?
Will a Handler postDelayed not being fired when CPU sleeps?
Numba code slower than pure python
How to speed up android ndk builds
Why is array item assignment degrading C# program performance?
Does this mean that Java Math.floor is extremely slow?
Hibernate SQL In clause making CPU usage to 100%

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!