Problem with UNION ALL

Hi,
I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
Among them one SELECT query is making more time.
If the same query is run seperately, it works fine in much less time
and the row count in the execution plan is small with a inner loop
join.
But when used with other select statements using UNION ALL,
the row count goes in thousands resulting in a HASH join.
Could u suggest any possible reason?
Thanks,
Dutt.Dutt
Do the SELECTs have WHERE conditions? How big is the output? Can you post
am example here?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> Hi,
> I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> Among them one SELECT query is making more time.
> If the same query is run seperately, it works fine in much less time
> and the row count in the execution plan is small with a inner loop
> join.
> But when used with other select statements using UNION ALL,
> the row count goes in thousands resulting in a HASH join.
> Could u suggest any possible reason?
> Thanks,
> Dutt.
>|||Hi Uri,
Yeah, some SELECTs have WHERE conditions and other SELECTs are just
SELECT NULLs.
Output is 4line XML.(17 rows without XML).
XML option has nothing to do with it.
Thanks,
Dutt.
On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt
> Do the SELECTs have WHERE conditions? How big is the output? Can you post
> am example here?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>
> > Hi,
> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> > Among them one SELECT query is making more time.
> > If the same query is run seperately, it works fine in much less time
> > and the row count in the execution plan is small with a inner loop
> > join.
> > But when used with other select statements using UNION ALL,
> > the row count goes in thousands resulting in a HASH join.
> > Could u suggest any possible reason?
> > Thanks,
> > Dutt.- Hide quoted text -
> - Show quoted text -|||Dutt
>> > the row count goes in thousands resulting in a HASH join.
As I see here you also have some JOIN within UNION ALL statement, have you
tried to created a nonclusterd indexes
on referenced tables (FK)?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
> Hi Uri,
> Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> SELECT NULLs.
> Output is 4line XML.(17 rows without XML).
> XML option has nothing to do with it.
> Thanks,
> Dutt.
>
> On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Dutt
>> Do the SELECTs have WHERE conditions? How big is the output? Can you
>> post
>> am example here?
>> "Dutt" <Mr.D...@.gmail.com> wrote in message
>> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>>
>> > Hi,
>> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
>> > Among them one SELECT query is making more time.
>> > If the same query is run seperately, it works fine in much less time
>> > and the row count in the execution plan is small with a inner loop
>> > join.
>> > But when used with other select statements using UNION ALL,
>> > the row count goes in thousands resulting in a HASH join.
>> > Could u suggest any possible reason?
>> > Thanks,
>> > Dutt.- Hide quoted text -
>> - Show quoted text -
>|||Yeah, Uri,
Here the joining key is Primary Key itself and no other key is
available between those tables.
And one doubt, is there any problem if we have too many UNION ALLs.
Thanks
Dutt
On Apr 10, 2:07 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt>> > the row count goes in thousands resulting in a HASH join.
> As I see here you also have some JOIN within UNION ALL statement, have you
> tried to created a nonclusterd indexes
> on referenced tables (FK)?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
>
> > Hi Uri,
> > Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> > SELECT NULLs.
> > Output is 4line XML.(17 rows without XML).
> > XML option has nothing to do with it.
> > Thanks,
> > Dutt.
> > On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Dutt
> >> Do the SELECTs have WHERE conditions? How big is the output? Can you
> >> post
> >> am example here?
> >> "Dutt" <Mr.D...@.gmail.com> wrote in message
> >>news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> >> > Hi,
> >> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> >> > Among them one SELECT query is making more time.
> >> > If the same query is run seperately, it works fine in much less time
> >> > and the row count in the execution plan is small with a inner loop
> >> > join.
> >> > But when used with other select statements using UNION ALL,
> >> > the row count goes in thousands resulting in a HASH join.
> >> > Could u suggest any possible reason?
> >> > Thanks,
> >> > Dutt.- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -