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

Issue with subscription and List

Hello,
I have created a report that includes a table within a list. The report
always renders correctly and as expected when accessed through a url. The
report never renders or gets emailed if rendered through a subscription. In
the log file I noticed the following error "The processing of group
expression for the list 'list1' cannot be performed. The comparison failed.
Please check the data type returned by group expression." If I remove the
List control, the report gets rendered correctly and emailed.
Can anyone shed some light on this problem?If you can create a sample report with this behavior based on either pubs or
northwind, we can take a look at it. You can send it to me (remove online
from my address)
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"JohnG" <JohnG@.discussions.microsoft.com> wrote in message
news:A209CD95-6710-4F72-B606-19ADA9CABE4B@.microsoft.com...
> Hello,
> I have created a report that includes a table within a list. The report
> always renders correctly and as expected when accessed through a url. The
> report never renders or gets emailed if rendered through a subscription.
> In
> the log file I noticed the following error "The processing of group
> expression for the list 'list1' cannot be performed. The comparison
> failed.
> Please check the data type returned by group expression." If I remove the
> List control, the report gets rendered correctly and emailed.
> Can anyone shed some light on this problem?