Does the number of columns in a view affect performance?
I have a view that pulls about 200 columns from a table, not a join. Processes using the view only use about 10 columns. Does the extra 190 columns have a significant performance impact using the view?
EDIT: Just for clarification based on the original comment by the commenter, the query in his proc only uses 10 columns out of 200. The question is, is this still causing performance degradation since the base view contains 200 columns or does the optimizer know to only use 10 columns and ignore the view out of 190 others?
Thank,
Chris
source to share
190 extra columns will definitely affect your performance. Adam does a pretty nice job explaining this on his blog: http://jahaines.blogspot.com/2009/06/superfluous-columns-more-than-bad-habit.html
source to share
First of all, if your view restricts your use of the WHERE clause, you can probably suffer a performance bottleneck, at least not being able to use a good index on your 10 columns if it collides with the view's own index.
If the view just delimits the columns but doesn't have a WHERE clause, that's vague - see below for details:
Based on this article , I am guessing that you will suffer a penalty as the view will NOT necessarily be compiled using your 10 columns and you can inherit the bad query plan.
It's very easy to check:
-
Run the request
select * from myView where someNonIndexedColumn = someValue
(make sure the column in the where clause is NOT in any of the indices on the original table).
-
Run the query above with a query plan and make sure it does a table scan.
-
Now select a couple of columns that are in the index on the original table for example. make sure to query them to use coverage index. Let's say C1 and C2 in index I1.
-
Run
select C1, C2 from myTable where C1 = x and C2 = Y
with the query plan and make sure it uses the I1 index as the coverage index.
-
Execute
select C1, C2 from myView where C1 = x and C2 = Y
with a query plan and check if it will do a table scan or I1 as a coverage index.
My suspicion is that it will perform a table scan, in which case you would respond with "extr 190 columns is Bad Thing For Performance" - basically all the negatives in Ryan Fonnett's linked article relate to your view.
If (unlikely) it uses the coverage index at # 5, then the fact that thew has 190 columsn doesn't matter.
source to share
It all depends, of course. This will always have consequences. But if we're talking about an application running on the same server as the database, and on columns where each contains multiple bytes, the impact shouldn't be important.
On the other hand, if we're talking about a client over the network to access the database and fetch 190 extra columns like (line * 255), then you're in big trouble if your network admin can catch you.
In any case, it is not very elegant to query for so many unnecessary columns. Why not adapt your query so that it only queries the columns you want. I am assuming that you are using "select * from ..." which gives rise to another problem: when someone changes the view (adding columns or removing columns) your program will block.
Rather than asking us, this is something you should check for yourself, as the answer can highly depend on your database structure and view, as well as the structure of your request and your hardware. Test fetching four columns from a view by fetching them directly from their respective tables and you will see if you have a measurable performance difference. If you have views stacked on views, I suspect you will find a measurable value, it might not really matter if there is only one view.
source to share