0

I have a view containing 30 columns, and they are joined from 6 tables. The view has 1 million records. When I search from the view, it takes 5 seconds to process the query.

My questions are:

  1. How do views work? Does it join at the time of query execution, or do views contain the data like tables? (Something like a virtual table.)

  2. Is there any limitation on the view, like maximum number of joins, or maximum number of records?

  3. Instead of a view, if I create a new table, can I retrieve the records faster?

Drenmi
  • 8,492
  • 4
  • 42
  • 51
Martin
  • 3
  • 2
  • You should add indexes to make your query / view fast. – SLaks Jan 23 '19 at 02:42
  • 1
    All a regular view is, is a pre-written query, it doesn't generally affect performance see https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query – Dale K Jan 23 '19 at 03:26

2 Answers2

2

How do views work? Does it join at the time of query execution, or do views contain the data like tables? (Something like a virtual table.)

View contain no data at all, it's just a saved piece of code similar to stored procedure but "joinable". When you use a view in your query, the view definition is used, the name of view is substituted with view definition and execution plan is built for this query with substitution, you won't see any view mention in the plan, only based tables.

Is there any limitation on the view, like maximum number of joins, or maximum number of records?

Maximum number of tables for SELECT statement depends on your server version, you can check it here: Maximum Capacity Specifications for SQL Server, for example, it was equal to 256 in 2008, in SQL Server 2017 it's limited only by available resources.

A view can have a maximum of 1,024 columns as described here CREATE VIEW (Transact-SQL)

There is no limit on number of rows returned.

Instead of a view, if I create a new table, can I retrieve the records faster?

It depends. You should examine the execution plan for your query.

Note that if you just copy the data returned by the query from your view definition to a new table, it will not automatically update when the data in based tables changes.

To make your "copy of data" automatically updatable you can create clustered index on your view. This is called Indexed view and has some limitations.

In any case you should start from your query actual execution plan, in order to find where server waste time and to understand if it's wrong cardinality estimation or just a lack of indexes on base tables.

sepupic
  • 8,409
  • 1
  • 9
  • 20
0

Views are nothing but 'saved queries' that you can treat as tables

Yes: views may affect your speed, materializing your view in a table may lead you to better (read) performances as you can indexes and also avoid 'join' overhead.

My suggestion is to try both solutions, profile them, then optimize adding suggested indexes.

Drawbacks of materialization is the particular attention that has to be paid to insert/update (you have to update the backend then rebuild/update the frontend materialized table)

PS: Damien_The_Unbeliever suggested indexed views: I tell you they exists, but I've never used them so I'll not be able to help you if you choose to use those.

Dale K
  • 25,246
  • 15
  • 42
  • 71
DDS
  • 2,340
  • 16
  • 34
  • 1
    Of course, the major downside to materializing the view into a table is staleness. You ought at least to suggest the intermediate step of an indexed view (assuming the view meets the requirements for indexing) – Damien_The_Unbeliever Jan 23 '19 at 08:26
  • @Damien_The_Unbeliever Of courseI should have had, but as I've never used them I want to avoid my advices not be correct, so I prefer tell what I knoww and I'm used to. – DDS Jan 23 '19 at 08:31