1

I am still trying to wrap my head around exactly how views work and when it is best to use a view vs querying a table directly. Here is my scenario:

  1. All of the underlying data resides in a single table that stores three month's worth of data
  2. The table includes four columns: 'TagName', 'Alarm', 'Timestamp', and 'Value'; 'TagName' and 'Timestamp' are indexed
  3. There is a view of this table (no other tables involved) that shows one week's worth of data and combines 'Alarm' and 'Value' into a single column.
  4. The database used in this scenario is SQL Server.

I only need to retrieve 5 - 30 minutes worth of data. Is more efficient to write a query against the underlying table that combines 'Alarm' and 'Value' into a single column (like the view) and sets the time range dynamically, or query the existing view by passing in a time range? To me, the former seems like the way to go since the latter essentially requires two queries. Furthermore, in the second scenario, the first query (i.e. the view) would load an unnecessary number of values into memory.

user2774187
  • 21
  • 1
  • 5
  • Which DBMS are you using? MySQL is notoriously bad at optimizing queries using views. But only an explain can tell –  Jun 12 '14 at 13:14

2 Answers2

1

If your data in single table is not large then querying a table will be faster than creating a view first and querying it for required data as it will avoid one step.

If data is not much and columns in where clause are properly indexed then generally queries should go to tables directly(which is faster in most cases).

Views should be used when you have very large data in a single table and you need to operate on small subset very frequently. In this case views will fetch the required data only once and will work that thus this will help in minimizing re-execution of time taking search queries (on single table or a join)

Before reaching to a solution, please validate/understand your data,requirement and have a single run with both the approaches and compare the time (I think query on table should be the winner) and then take the decision.

Hope this helps.

Nachiket Kate
  • 8,473
  • 2
  • 27
  • 45
0

In general you should simplify queries as much as possible by using views. This makes management of your application simpler and also helps you avoid repetition of query logic (joins and WHERE clauses). However, views can be ill-suited for a particular query which leads to bad for performance as they might lead to unnecessary operations not needed in your particular query. Views are abused when they introduce unnecessary complexity.

user2672165
  • 2,986
  • 19
  • 27
  • Not advocating for or against views here, but I have resolved performance issues in the past by changing queries that use views to go against the base tables because the views are joining in multiple tables which the outer query does not need. – Brandon Jun 11 '14 at 19:34