I've never seen this before

I saw something today that I had never noticed before. A collegue of mine
and I were troubleshooting a query that was not performing as expected. The
query consisted of a Select from a complex view with a where in subquery.
In all, the query was taking about 30s. The subquery runs quickly when run
by itself. Heck, the Select from the views run quickly by itself.
We looked at the execution plan to try to figure out where we might be able
to add effeciencies. 16% of the total work was being done by the subquery.
This didn't seem to make sense. Then we noticed that the subquery data was
being joined into data stream as the view was being built! I guess I
expected the Optimizer to build the view, run the subquery, and loop through
the data to filter it. The Optimizer instead redefined the view on the fly
by joining in the subquery data. To test it we did a Select Into a temp
table with * from the view and then ran a second query filtering that data
according to the subquery. That test took 6 seconds.
Has anyone seen this type of behaviour before?
ChristianSQL Server has always implemented queries against views by combining
the SELECT that is the view with the SELECT that references the view.
Then it is up to the optimizer to make a good query plan out of that.
There are times the optimizer doesn't choose well, and a query
referencing a complex view such as you describe is certainly a prime
candicate for this problem.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 10:34:42 -0400, "Christian Smith"
<malekai101@.yahoo.com> wrote:

>I saw something today that I had never noticed before. A collegue of mine
>and I were troubleshooting a query that was not performing as expected. Th
e
>query consisted of a Select from a complex view with a where in subquery.
>In all, the query was taking about 30s. The subquery runs quickly when run
>by itself. Heck, the Select from the views run quickly by itself.
>We looked at the execution plan to try to figure out where we might be able
>to add effeciencies. 16% of the total work was being done by the subquery.
>This didn't seem to make sense. Then we noticed that the subquery data was
>being joined into data stream as the view was being built! I guess I
>expected the Optimizer to build the view, run the subquery, and loop throug
h
>the data to filter it. The Optimizer instead redefined the view on the fly
>by joining in the subquery data. To test it we did a Select Into a temp
>table with * from the view and then ran a second query filtering that data
>according to the subquery. That test took 6 seconds.
>Has anyone seen this type of behaviour before?
>Christian