Does the number of columns in a view impact performance?

Question

I have a view that pulls about 200 columns from a table, no joins. The procs that use the view only use about 10 columns from it. Does having the extra 190 columns have a significant impact on performance using the view?

EDIT: Just to clarify based on original questioner's comment, the query in his proc only uses 10 columns out of 200. The question is, does that still cause performance degradation because the underlying view contains 200 columns, or does the optimizer know to only use the 10 columns and ignores the view's knowledge of 190 others?

Thanks,

Chris

Answer 1

190 superfluous columns will definitely impact your performance. Adam does a pretty nice job in explaining this in his blog: http://jahaines.blogspot.com/2009/06/superfluous-columns-more-than-bad-habit.html

Answer 2

First of all, if your view restricts using a WHERE clause, you may likely suffer performance penalty, at the very least, due to inability to use good index on your 10 columns if it clashes with view's own used index.

If the view merely restricts the columns but has no WHERE clause, it is uncertain - see details below:

Based on this article, I'm inferring that you will suffer the penalty since the view will NOT necessarily be compiled using your 10 columns and you may inherit the bad query plan.

It is very easy to test:

1. Run a query

   select * from myView where someNonIndexedColumn = someValue

   (make sure that the column in the where clause is NOT in any of the indexes on the original table).

2. Run the query above with query plan on, and ensure that it does table scan.

3. Now, pick a couple of columns that ARE in an index on original table, e.g. make sure that the query on them should use the covering index. Say, C1 and C2 in index I1.

4. Run

   select C1, C2 from myTable where C1=x and C2=Y

   with the query plan on and make sure it uses the "I1" index as covering index.

5. Run

   select C1, C2 from myView where C1=x and C2=Y

   with the query plan on and check whether it will do a table scan or I1 as a covering index.

My suspicion is that it will do a table scan, in which case you answer is "extr 190 columns are Bad Thing For Performance" - basically, all of the negatives in Ryan Fonnett's linked article apply to your view.

If (unlikely) it uses covering index in #5, then the fact that thew has 190 columsn is irrelevant.

Answer 3

It all depends of course. It will always have an impact. But if we are talking about an application running on the same server as the database, and columns that each contain some bytes, the impact should not be to important.

On the other hand, if we are talking about a client running over a network to access a database, and extracting 190 extra columns like (string*255) then you are in big trouble if your network administrator can catch you alive.

In either case, it's not very elegant to query so many needless columns. Why not adapt your query so that is only asks for the needed columns. I presume you use "select * from ..." which generates an other problem: when someone changes the view (adds columns or deletes columns) your program will block.

Answer 4

Instead of asking us, this is something you should test for yourself as the answer may be highly dependent on the structure of your database and view and the structure of your query and the equipment mix you have. Test selecting the four columns from the view against selecting them directly from the tables involved and you will know if you have a measurable performance difference. If you have views stacked on views, I suspect you will find a measurable differnce, it may not be a big deal if there is only one view.