Query Performance when selecting from a view vs a table

Question

Query Performance when selecting from a view vs a table

Phill 61

Hi All, I have been searching the forums for previous posts on this issue but none of them seem to apply to my scenario. I thought I would post here in hopes that someone can help. I work in an organization with a lot of legacy databases where data is not stored in the correct format for their data types. E.G., dates are stored as numbers in yyymmdd format, some numbers are stored as text with embedded commas and bit values are stored as text 'True' and 'False'. In addition, some of the data is segmented across different databases and across multiple tables. When writing reports, I write a lot of data conversions and UNION queries to pull the data into a single datasets etc and up until now, I have been able to write this into the SQL Query designer of MS Report Builder/Visual Studio. I am about to start working with Power BI Desktop and it seems that this functionality has to be performed in a view. If I have a table with 250,000 + rows and I write a select query with numerous string and number conversions from multiple tables. If I only need a small subset of the data .e.g. a specific customer ID. Will the view have to build the entire dataset and perform the conversions and functions on every row of data before it would then return the smaller subset of the customers data? My concern is the performance of the view would be significantly slower to run when selecting from a view than it would to directly query the tables. How would this work? Would the performance be the same as querying the table directly or do I have a valid concern? Thanks

Accepted answer

0 additional answers

Your answer

Answer 1

Tom Phillips 17,771

There is no difference in SQL Server between writing a SELECT statement and putting that SELECT statement in a view. A view is basically a string macro for a select statement, in SQL Server. Other engines treat views differently.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-03-16T21:46:32.133+00:00

And to add to Tom's answer, if you have:

CREATE VIEW myview AS <huge_SELECT>

and then say

SELECT ... FROM myview WHERE id = 98

this is exactly the same as if you had said

<huge SELECT>
WHERE id = 98

The case when views start to get a problem is when people start to nest them, as this often leads to that the same table appears multiple times in the same query when there is no reason to.
Phill 61 Reputation points

2021-03-16T22:04:33.717+00:00

Thank you for the input. I always thought views had to build the entire dataset before the WHERE clause would applied so this is really good information to have.
Jeffrey Williams 1,896 Reputation points

2021-06-21T21:33:40.16+00:00

It might be a benefit to you to pre-process the data into a more normalized structure. That is - once a day schedule a process that builds a table with the correct data types and the subset of columns and rows that you need for your report. Then use that table in PowerBI instead of the view the converts the data.

This would work for any set of data that doesn't need to be real or near-real time data. Most reports will not require data from the current day - so creating a table with data up to midnight of the previous day would satisfy those queries.

Share via

Query Performance when selecting from a view vs a table

0 additional answers

Your answer