View behaviour in join

[Marcelo Noga]

Hi Everybody,

I have a complex view, that includes a "group by" clause. I'm trying
to join this view with a table, in a very simple query.

The problem is that the optimizer is not using the table data as input
for the view (I expect this because I have arguments for the table,
but not for the view), but executing the view in a different step and
then joining to the table by a merge/hash join. This is obviously very
slow.

I tried to force nested loops by using hints but it still doesn't use
the table data as input.

Has anybody ever seen this?
Thanks in advance...

[Erland Sommarskog]

Marcelo Noga (no**@mpsinf.com.br) writes:

I have a complex view, that includes a "group by" clause. I'm trying
to join this view with a table, in a very simple query.

The problem is that the optimizer is not using the table data as input
for the view (I expect this because I have arguments for the table,
but not for the view), but executing the view in a different step and
then joining to the table by a merge/hash join. This is obviously very
slow.

I tried to force nested loops by using hints but it still doesn't use
the table data as input.

Theory has it that the view is just a macro which is just pasted into
the query, although it does not always really work like that.

But I think you need to post you view and query, so we know what you are
talking about.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> ... executing the view in a different step and then joining to the
table by a merge/hash join. <<

Most optimizers will do it this way because of the GROUP BY. Can you

1) use a derived table to fool the optimizer.

2) expand the view and see if you can move the GROUP BY to the outer
query

[Marcelo Noga]

Thank you all, but I've already found a solution to it.

I don't know why, but changing the regular join to an outer join (with
the view being the inner "table") made the optimizer to work fine.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!