AVOID merge joins

26 May 2005 1:54 PM

Jan

Hi,

Does anyone know of a way to AVOID merge joins?

I have a complex query that works fine on one system, but not on another.
After inspection, I found that the difference lies in different query plans
generated by the query optimizer. On the failing system, a merge join is
used, but since the total size of the columns is more than 8k, I get the
error:

Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8571, which is greater than the allowable maximum
of 8094.

With query optimizer hints I can favour an optimization, but is there also a
way to AVOID one?
(It should be a query optimizer hint on the complete SELECT, not on a
specific table: the merge join is used on an intermediate step of the query,
not on an identified table.)

Many thanks,
Jan

26 May 2005 2:19 PM

Jan

In the mean time, I have found that I can use OPTION(ROBUST PLAN) in the
select.
Does anyone know the syntax when I would want to use it in a view?

Thanks,
Jan

Show quoteHide quote

"Jan" wrote:

> Hi,
>
> Does anyone know of a way to AVOID merge joins?
>
> I have a complex query that works fine on one system, but not on another.
> After inspection, I found that the difference lies in different query plans
> generated by the query optimizer. On the failing system, a merge join is
> used, but since the total size of the columns is more than 8k, I get the
> error:
>
> Server: Msg 1540, Level 16, State 1, Line 1
> Cannot sort a row of size 8571, which is greater than the allowable maximum
> of 8094.
>
> With query optimizer hints I can favour an optimization, but is there also a
> way to AVOID one?
> (It should be a query optimizer hint on the complete SELECT, not on a
> specific table: the merge join is used on an intermediate step of the query,
> not on an identified table.)
>
> Many thanks,
> Jan
>
>

26 May 2005 3:08 PM

SkyWalker

Basically it seems an issue that you have is not with type of join
but with row size. When you use ROBUST PLAN option optimizer will
try to use the max possible row size at the expense of actual query
performance.
I would recommend to review query strategy to avoid of using huge row sizes.

But in a mean time you may try to use join type hint to prevent of using
MERGE JOIN
or use OPTION with particular JOIN type.

Regards.

Show quoteHide quote

"Jan" wrote:

> In the mean time, I have found that I can use OPTION(ROBUST PLAN) in the
> select.
> Does anyone know the syntax when I would want to use it in a view?
>
> Thanks,
> Jan
>
> "Jan" wrote:
>
> > Hi,
> >
> > Does anyone know of a way to AVOID merge joins?
> >
> > I have a complex query that works fine on one system, but not on another.
> > After inspection, I found that the difference lies in different query plans
> > generated by the query optimizer. On the failing system, a merge join is
> > used, but since the total size of the columns is more than 8k, I get the
> > error:
> >
> > Server: Msg 1540, Level 16, State 1, Line 1
> > Cannot sort a row of size 8571, which is greater than the allowable maximum
> > of 8094.
> >
> > With query optimizer hints I can favour an optimization, but is there also a
> > way to AVOID one?
> > (It should be a query optimizer hint on the complete SELECT, not on a
> > specific table: the merge join is used on an intermediate step of the query,
> > not on an identified table.)
> >
> > Many thanks,
> > Jan
> >
> >