Performance tuning views on SQL Server (moved from MySql forum)

Databases
1

performance tuning for views

Views can slow down queries for several different reasons. For example, let’s look at these two SELECT statements:

[COLOR=blue]SELECT * FROM table_name

SELECT * FROM view_name

Which is faster?

If you test it, you will find that the first SELECT statement is faster, although the execution plan for both of them will be the same. How can that be? This is because it takes SQL Server extra work (such as looking up data in the system tables) before it can execute the view. This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don’t, because some of the work SQL Server is doing is hidden.
Another way views can hurt performance is when JOINs or UNIONs are used, and you don’t intend to use all of the columns. This results in SQL Server performing unnecessary work (such as an unnecessary JOIN or UNION), slowing down the performance
[/COLOR]

2

I have two questions.

How much slower, how much extra work? Is it a significant amount and even then might there be some circumstances where it’s justified?

Perhaps more importantly, has the test been done not using the evil * ??

3

I’ll add that the query optimizer is pretty smart and it wouldn’t be out of the question for it to “speed up” a bit as it caches the rather tiny lookup stuff.

That and if you are that far into micro optimization mode, you might be better off writing your next app.