Hi,
Suppose that I have a Master table with almost 40,000 records. The Details
table contains 50,000 records. My query Inner Joins these two table and the
query optimizer chooses Hash Join algorithm to perform the query.
Obviously it will not be a good idea to try to change the plan to a Nested
Loop because the number of rows in tables are large and close to each other.
Therefore Merge Join will (probably) be the best algorithm. I must create an
index on Details table beginning with FK column and including other columns
to cover the query.
The problem is that sometimes the number of required columns are more that
allowable quantity or the length of index exceeds 900 bytes.
Should I convince my boss to be satisfy with Hash Join or there's a
solution?
Any help will be greatly appreciated.
LeilaAnother solution would be to have a clustered index on the foreign table
starting with the foreign key column.
FYI, in SQL Server 2005 you will be able to create indexes with included
non-key columns for covering purposes, and the 900 bytes limitation does not
apply to included non-key columns.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||Thanks Itzik,
I thought about it, but I have several queries like that, I cannot have a
clustered index for each ;-)
Any solution before 2005?!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> Another solution would be to have a clustered index on the foreign table
> starting with the foreign key column.
> FYI, in SQL Server 2005 you will be able to create indexes with included
> non-key columns for covering purposes, and the 900 bytes limitation does
not
> apply to included non-key columns.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
> > the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
> > other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create
> > an
> > index on Details table beginning with FK column and including other
> > columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
>|||<snip>... and including other columns to cover the query.</snip>
WHy do you need it to be a covering index? That is useful when the number
of columns required by a query is small, but if a query returns a large
number of columns, that is not a good idea, and whwther or not a covering
index exists will not affect the type of join algorithm the optmizer uses...
"Leila" wrote:
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each other.
> Therefore Merge Join will (probably) be the best algorithm. I must create an
> index on Details table beginning with FK column and including other columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>
>|||Indexed views is another option.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Thanks Itzik,
> I thought about it, but I have several queries like that, I cannot have a
> clustered index for each ;-)
> Any solution before 2005?!
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> Another solution would be to have a clustered index on the foreign table
>> starting with the foreign key column.
>> FYI, in SQL Server 2005 you will be able to create indexes with included
>> non-key columns for covering purposes, and the 900 bytes limitation does
> not
>> apply to included non-key columns.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Suppose that I have a Master table with almost 40,000 records. The
> Details
>> > table contains 50,000 records. My query Inner Joins these two table and
>> > the
>> > query optimizer chooses Hash Join algorithm to perform the query.
>> > Obviously it will not be a good idea to try to change the plan to a
> Nested
>> > Loop because the number of rows in tables are large and close to each
>> > other.
>> > Therefore Merge Join will (probably) be the best algorithm. I must
> create
>> > an
>> > index on Details table beginning with FK column and including other
>> > columns
>> > to cover the query.
>> > The problem is that sometimes the number of required columns are more
> that
>> > allowable quantity or the length of index exceeds 900 bytes.
>> > Should I convince my boss to be satisfy with Hash Join or there's a
>> > solution?
>> > Any help will be greatly appreciated.
>> > Leila
>> >
>> >
>>
>|||Are you trying to join the entire table at a time? If so it might be
cheaper overall to just go Hash Join. It is a pretty good algorithm, though
you are right the Merge Join will be good. I wouldn't try to index all rows
necessarily because that will be costly to maintain.
The Nested loops join should be the best algorithm for a simple one to many
with low cardinality (which you should have since your parent table only has
4/5 of the number of rows that the child has) and a reasonable join key. Do
you have an index on the foreign key now? Can you post the table
structures?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Suppose that I have a Master table with almost 40,000 records. The Details
> table contains 50,000 records. My query Inner Joins these two table and
> the
> query optimizer chooses Hash Join algorithm to perform the query.
> Obviously it will not be a good idea to try to change the plan to a Nested
> Loop because the number of rows in tables are large and close to each
> other.
> Therefore Merge Join will (probably) be the best algorithm. I must create
> an
> index on Details table beginning with FK column and including other
> columns
> to cover the query.
> The problem is that sometimes the number of required columns are more that
> allowable quantity or the length of index exceeds 900 bytes.
> Should I convince my boss to be satisfy with Hash Join or there's a
> solution?
> Any help will be greatly appreciated.
> Leila
>|||If the index is not covering, then I suppose bookmark lookup will be
required to gather other columns. Can lookup happen in a merge join?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> <snip>... and including other columns to cover the query.</snip>
> WHy do you need it to be a covering index? That is useful when the number
> of columns required by a query is small, but if a query returns a large
> number of columns, that is not a good idea, and whwther or not a covering
> index exists will not affect the type of join algorithm the optmizer
uses...
>
> "Leila" wrote:
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create an
> > index on Details table beginning with FK column and including other
columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
> >|||Thanks Louis!
> Are you trying to join the entire table at a time?
Yes, I need to.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Nested loop should be good when the Master table is small. I don't think if
40,000 index seeks on Details table can result in a good performance.
> Do you have an index on the foreign key now?
Yes but it doesn't help.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Are you trying to join the entire table at a time? If so it might be
> cheaper overall to just go Hash Join. It is a pretty good algorithm,
though
> you are right the Merge Join will be good. I wouldn't try to index all
rows
> necessarily because that will be costly to maintain.
> The Nested loops join should be the best algorithm for a simple one to
many
> with low cardinality (which you should have since your parent table only
has
> 4/5 of the number of rows that the child has) and a reasonable join key.
Do
> you have an index on the foreign key now? Can you post the table
> structures?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Suppose that I have a Master table with almost 40,000 records. The
Details
> > table contains 50,000 records. My query Inner Joins these two table and
> > the
> > query optimizer chooses Hash Join algorithm to perform the query.
> > Obviously it will not be a good idea to try to change the plan to a
Nested
> > Loop because the number of rows in tables are large and close to each
> > other.
> > Therefore Merge Join will (probably) be the best algorithm. I must
create
> > an
> > index on Details table beginning with FK column and including other
> > columns
> > to cover the query.
> > The problem is that sometimes the number of required columns are more
that
> > allowable quantity or the length of index exceeds 900 bytes.
> > Should I convince my boss to be satisfy with Hash Join or there's a
> > solution?
> > Any help will be greatly appreciated.
> > Leila
> >
> >
>|||Great! Do you mean I create an indexed view on all required columns of
Details table and join the Master with this view or ...?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> Indexed views is another option.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> > Thanks Itzik,
> > I thought about it, but I have several queries like that, I cannot have
a
> > clustered index for each ;-)
> > Any solution before 2005?!
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> Another solution would be to have a clustered index on the foreign
table
> >> starting with the foreign key column.
> >>
> >> FYI, in SQL Server 2005 you will be able to create indexes with
included
> >> non-key columns for covering purposes, and the 900 bytes limitation
does
> > not
> >> apply to included non-key columns.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Suppose that I have a Master table with almost 40,000 records. The
> > Details
> >> > table contains 50,000 records. My query Inner Joins these two table
and
> >> > the
> >> > query optimizer chooses Hash Join algorithm to perform the query.
> >> > Obviously it will not be a good idea to try to change the plan to a
> > Nested
> >> > Loop because the number of rows in tables are large and close to each
> >> > other.
> >> > Therefore Merge Join will (probably) be the best algorithm. I must
> > create
> >> > an
> >> > index on Details table beginning with FK column and including other
> >> > columns
> >> > to cover the query.
> >> > The problem is that sometimes the number of required columns are more
> > that
> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> > Should I convince my boss to be satisfy with Hash Join or there's a
> >> > solution?
> >> > Any help will be greatly appreciated.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yes, sure...
"Leila" wrote:
> If the index is not covering, then I suppose bookmark lookup will be
> required to gather other columns. Can lookup happen in a merge join?
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:995BAB85-A165-4239-A14F-8A263CC2F115@.microsoft.com...
> > <snip>... and including other columns to cover the query.</snip>
> >
> > WHy do you need it to be a covering index? That is useful when the number
> > of columns required by a query is small, but if a query returns a large
> > number of columns, that is not a good idea, and whwther or not a covering
> > index exists will not affect the type of join algorithm the optmizer
> uses...
> >
> >
> > "Leila" wrote:
> >
> > > Hi,
> > > Suppose that I have a Master table with almost 40,000 records. The
> Details
> > > table contains 50,000 records. My query Inner Joins these two table and
> the
> > > query optimizer chooses Hash Join algorithm to perform the query.
> > > Obviously it will not be a good idea to try to change the plan to a
> Nested
> > > Loop because the number of rows in tables are large and close to each
> other.
> > > Therefore Merge Join will (probably) be the best algorithm. I must
> create an
> > > index on Details table beginning with FK column and including other
> columns
> > > to cover the query.
> > > The problem is that sometimes the number of required columns are more
> that
> > > allowable quantity or the length of index exceeds 900 bytes.
> > > Should I convince my boss to be satisfy with Hash Join or there's a
> > > solution?
> > > Any help will be greatly appreciated.
> > > Leila
> > >
> > >
> > >
>
>|||That's one option. The other (in case it's an Enterprise edition), is to
continue querying the base tables.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Great! Do you mean I create an indexed view on all required columns of
> Details table and join the Master with this view or ...?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> Indexed views is another option.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> > Thanks Itzik,
>> > I thought about it, but I have several queries like that, I cannot have
> a
>> > clustered index for each ;-)
>> > Any solution before 2005?!
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> Another solution would be to have a clustered index on the foreign
> table
>> >> starting with the foreign key column.
>> >>
>> >> FYI, in SQL Server 2005 you will be able to create indexes with
> included
>> >> non-key columns for covering purposes, and the 900 bytes limitation
> does
>> > not
>> >> apply to included non-key columns.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> > Suppose that I have a Master table with almost 40,000 records. The
>> > Details
>> >> > table contains 50,000 records. My query Inner Joins these two table
> and
>> >> > the
>> >> > query optimizer chooses Hash Join algorithm to perform the query.
>> >> > Obviously it will not be a good idea to try to change the plan to a
>> > Nested
>> >> > Loop because the number of rows in tables are large and close to
>> >> > each
>> >> > other.
>> >> > Therefore Merge Join will (probably) be the best algorithm. I must
>> > create
>> >> > an
>> >> > index on Details table beginning with FK column and including other
>> >> > columns
>> >> > to cover the query.
>> >> > The problem is that sometimes the number of required columns are
>> >> > more
>> > that
>> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> > Should I convince my boss to be satisfy with Hash Join or there's a
>> >> > solution?
>> >> > Any help will be greatly appreciated.
>> >> > Leila
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Sorry I didn't get it, could please tell me more!
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> That's one option. The other (in case it's an Enterprise edition), is to
> continue querying the base tables.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> > Great! Do you mean I create an indexed view on all required columns of
> > Details table and join the Master with this view or ...?
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> >> Indexed views is another option.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> > Thanks Itzik,
> >> > I thought about it, but I have several queries like that, I cannot
have
> > a
> >> > clustered index for each ;-)
> >> > Any solution before 2005?!
> >> >
> >> >
> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> >> > message
> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> Another solution would be to have a clustered index on the foreign
> > table
> >> >> starting with the foreign key column.
> >> >>
> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
> > included
> >> >> non-key columns for covering purposes, and the 900 bytes limitation
> > does
> >> > not
> >> >> apply to included non-key columns.
> >> >>
> >> >> --
> >> >> BG, SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> > Suppose that I have a Master table with almost 40,000 records. The
> >> > Details
> >> >> > table contains 50,000 records. My query Inner Joins these two
table
> > and
> >> >> > the
> >> >> > query optimizer chooses Hash Join algorithm to perform the query.
> >> >> > Obviously it will not be a good idea to try to change the plan to
a
> >> > Nested
> >> >> > Loop because the number of rows in tables are large and close to
> >> >> > each
> >> >> > other.
> >> >> > Therefore Merge Join will (probably) be the best algorithm. I must
> >> > create
> >> >> > an
> >> >> > index on Details table beginning with FK column and including
other
> >> >> > columns
> >> >> > to cover the query.
> >> >> > The problem is that sometimes the number of required columns are
> >> >> > more
> >> > that
> >> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> >> > Should I convince my boss to be satisfy with Hash Join or there's
a
> >> >> > solution?
> >> >> > Any help will be greatly appreciated.
> >> >> > Leila
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||How slow is it now, and how much faster do you want it? That is a tall
order to join so many rows at once. It sounds like you might be stuck where
you are. Can you post the plan?
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Leila" <leilas@.hotpop.com> wrote in message
news:OCSOPHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Thanks Louis!
>> Are you trying to join the entire table at a time?
> Yes, I need to.
>> The Nested loops join should be the best algorithm for a simple one to
> many
>> with low cardinality (which you should have since your parent table only
> has
>> 4/5 of the number of rows that the child has) and a reasonable join key.
> Nested loop should be good when the Master table is small. I don't think
> if
> 40,000 index seeks on Details table can result in a good performance.
>> Do you have an index on the foreign key now?
> Yes but it doesn't help.
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uEJJl$qRFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Are you trying to join the entire table at a time? If so it might be
>> cheaper overall to just go Hash Join. It is a pretty good algorithm,
> though
>> you are right the Merge Join will be good. I wouldn't try to index all
> rows
>> necessarily because that will be costly to maintain.
>> The Nested loops join should be the best algorithm for a simple one to
> many
>> with low cardinality (which you should have since your parent table only
> has
>> 4/5 of the number of rows that the child has) and a reasonable join key.
> Do
>> you have an index on the foreign key now? Can you post the table
>> structures?
>>
>> --
>> ----
> --
>> Louis Davidson - drsql@.hotmail.com
>> SQL Server MVP
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Blog - http://spaces.msn.com/members/drsql/
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services. All other replies may be ignored :)
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Suppose that I have a Master table with almost 40,000 records. The
> Details
>> > table contains 50,000 records. My query Inner Joins these two table and
>> > the
>> > query optimizer chooses Hash Join algorithm to perform the query.
>> > Obviously it will not be a good idea to try to change the plan to a
> Nested
>> > Loop because the number of rows in tables are large and close to each
>> > other.
>> > Therefore Merge Join will (probably) be the best algorithm. I must
> create
>> > an
>> > index on Details table beginning with FK column and including other
>> > columns
>> > to cover the query.
>> > The problem is that sometimes the number of required columns are more
> that
>> > allowable quantity or the length of index exceeds 900 bytes.
>> > Should I convince my boss to be satisfy with Hash Join or there's a
>> > solution?
>> > Any help will be greatly appreciated.
>> > Leila
>> >
>> >
>>
>|||Sure.
In Enterprise edition the optimizer can consider using an indexed view even
if you don't query the view directly, rather the base tables.
It doesn't work in all cases, i.e., there are still cases where the
optimizer reverts to the base tables and not the indexed view, but you can
try and hope for the best.
BTW, SQL Server 2005 does a better job at this, and it uses the indexed view
in more cases.
If it's not an Enterprise edition, in order to use the indexed view, you
must:
1. Query the view directly
2. Specify the NOEXPAND hint
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
> Sorry I didn't get it, could please tell me more!
>
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> That's one option. The other (in case it's an Enterprise edition), is to
>> continue querying the base tables.
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
>> > Great! Do you mean I create an indexed view on all required columns of
>> > Details table and join the Master with this view or ...?
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> >> Indexed views is another option.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> > Thanks Itzik,
>> >> > I thought about it, but I have several queries like that, I cannot
> have
>> > a
>> >> > clustered index for each ;-)
>> >> > Any solution before 2005?!
>> >> >
>> >> >
>> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> >> > message
>> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> Another solution would be to have a clustered index on the foreign
>> > table
>> >> >> starting with the foreign key column.
>> >> >>
>> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
>> > included
>> >> >> non-key columns for covering purposes, and the 900 bytes limitation
>> > does
>> >> > not
>> >> >> apply to included non-key columns.
>> >> >>
>> >> >> --
>> >> >> BG, SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Hi,
>> >> >> > Suppose that I have a Master table with almost 40,000 records.
>> >> >> > The
>> >> > Details
>> >> >> > table contains 50,000 records. My query Inner Joins these two
> table
>> > and
>> >> >> > the
>> >> >> > query optimizer chooses Hash Join algorithm to perform the query.
>> >> >> > Obviously it will not be a good idea to try to change the plan to
> a
>> >> > Nested
>> >> >> > Loop because the number of rows in tables are large and close to
>> >> >> > each
>> >> >> > other.
>> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
>> >> >> > must
>> >> > create
>> >> >> > an
>> >> >> > index on Details table beginning with FK column and including
> other
>> >> >> > columns
>> >> >> > to cover the query.
>> >> >> > The problem is that sometimes the number of required columns are
>> >> >> > more
>> >> > that
>> >> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> >> > Should I convince my boss to be satisfy with Hash Join or there's
> a
>> >> >> > solution?
>> >> >> > Any help will be greatly appreciated.
>> >> >> > Leila
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Thanks indeed!
What if i create an indexed view from the main INNER JOIN query rather than
creating indexed view from only Details table?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ePgHVk1RFHA.904@.tk2msftngp13.phx.gbl...
> Sure.
> In Enterprise edition the optimizer can consider using an indexed view
even
> if you don't query the view directly, rather the base tables.
> It doesn't work in all cases, i.e., there are still cases where the
> optimizer reverts to the base tables and not the indexed view, but you can
> try and hope for the best.
> BTW, SQL Server 2005 does a better job at this, and it uses the indexed
view
> in more cases.
> If it's not an Enterprise edition, in order to use the indexed view, you
> must:
> 1. Query the view directly
> 2. Specify the NOEXPAND hint
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Sorry I didn't get it, could please tell me more!
> >
> >
> >
> >
> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> > message
> > news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> That's one option. The other (in case it's an Enterprise edition), is
to
> >> continue querying the base tables.
> >>
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
> >> > Great! Do you mean I create an indexed view on all required columns
of
> >> > Details table and join the Master with this view or ...?
> >> >
> >> >
> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> >> > message
> >> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
> >> >> Indexed views is another option.
> >> >>
> >> >> --
> >> >> BG, SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> >> >> > Thanks Itzik,
> >> >> > I thought about it, but I have several queries like that, I cannot
> > have
> >> > a
> >> >> > clustered index for each ;-)
> >> >> > Any solution before 2005?!
> >> >> >
> >> >> >
> >> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote
in
> >> >> > message
> >> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
> >> >> >> Another solution would be to have a clustered index on the
foreign
> >> > table
> >> >> >> starting with the foreign key column.
> >> >> >>
> >> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
> >> > included
> >> >> >> non-key columns for covering purposes, and the 900 bytes
limitation
> >> > does
> >> >> > not
> >> >> >> apply to included non-key columns.
> >> >> >>
> >> >> >> --
> >> >> >> BG, SQL Server MVP
> >> >> >> www.SolidQualityLearning.com
> >> >> >>
> >> >> >>
> >> >> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> >> >> > Hi,
> >> >> >> > Suppose that I have a Master table with almost 40,000 records.
> >> >> >> > The
> >> >> > Details
> >> >> >> > table contains 50,000 records. My query Inner Joins these two
> > table
> >> > and
> >> >> >> > the
> >> >> >> > query optimizer chooses Hash Join algorithm to perform the
query.
> >> >> >> > Obviously it will not be a good idea to try to change the plan
to
> > a
> >> >> > Nested
> >> >> >> > Loop because the number of rows in tables are large and close
to
> >> >> >> > each
> >> >> >> > other.
> >> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
> >> >> >> > must
> >> >> > create
> >> >> >> > an
> >> >> >> > index on Details table beginning with FK column and including
> > other
> >> >> >> > columns
> >> >> >> > to cover the query.
> >> >> >> > The problem is that sometimes the number of required columns
are
> >> >> >> > more
> >> >> > that
> >> >> >> > allowable quantity or the length of index exceeds 900 bytes.
> >> >> >> > Should I convince my boss to be satisfy with Hash Join or
there's
> > a
> >> >> >> > solution?
> >> >> >> > Any help will be greatly appreciated.
> >> >> >> > Leila
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Even better. The indexed view can cover the whole join query saving the need
for rejoining every time you query.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23cSBjY4RFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Thanks indeed!
> What if i create an indexed view from the main INNER JOIN query rather
> than
> creating indexed view from only Details table?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:ePgHVk1RFHA.904@.tk2msftngp13.phx.gbl...
>> Sure.
>> In Enterprise edition the optimizer can consider using an indexed view
> even
>> if you don't query the view directly, rather the base tables.
>> It doesn't work in all cases, i.e., there are still cases where the
>> optimizer reverts to the base tables and not the indexed view, but you
>> can
>> try and hope for the best.
>> BTW, SQL Server 2005 does a better job at this, and it uses the indexed
> view
>> in more cases.
>> If it's not an Enterprise edition, in order to use the indexed view, you
>> must:
>> 1. Query the view directly
>> 2. Specify the NOEXPAND hint
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:Ob0uibwRFHA.576@.TK2MSFTNGP15.phx.gbl...
>> > Sorry I didn't get it, could please tell me more!
>> >
>> >
>> >
>> >
>> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> > message
>> > news:#90aRmvRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> That's one option. The other (in case it's an Enterprise edition), is
> to
>> >> continue querying the base tables.
>> >>
>> >> --
>> >> BG, SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> news:ucfOGHsRFHA.3120@.TK2MSFTNGP10.phx.gbl...
>> >> > Great! Do you mean I create an indexed view on all required columns
> of
>> >> > Details table and join the Master with this view or ...?
>> >> >
>> >> >
>> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
>> >> > message
>> >> > news:uslJpOpRFHA.3880@.tk2msftngp13.phx.gbl...
>> >> >> Indexed views is another option.
>> >> >>
>> >> >> --
>> >> >> BG, SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> news:%23ZlieboRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Thanks Itzik,
>> >> >> > I thought about it, but I have several queries like that, I
>> >> >> > cannot
>> > have
>> >> > a
>> >> >> > clustered index for each ;-)
>> >> >> > Any solution before 2005?!
>> >> >> >
>> >> >> >
>> >> >> > "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote
> in
>> >> >> > message
>> >> >> > news:em72QUoRFHA.164@.TK2MSFTNGP12.phx.gbl...
>> >> >> >> Another solution would be to have a clustered index on the
> foreign
>> >> > table
>> >> >> >> starting with the foreign key column.
>> >> >> >>
>> >> >> >> FYI, in SQL Server 2005 you will be able to create indexes with
>> >> > included
>> >> >> >> non-key columns for covering purposes, and the 900 bytes
> limitation
>> >> > does
>> >> >> > not
>> >> >> >> apply to included non-key columns.
>> >> >> >>
>> >> >> >> --
>> >> >> >> BG, SQL Server MVP
>> >> >> >> www.SolidQualityLearning.com
>> >> >> >>
>> >> >> >>
>> >> >> >> "Leila" <leilas@.hotpop.com> wrote in message
>> >> >> >> news:eI50yOoRFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> >> >> >> > Hi,
>> >> >> >> > Suppose that I have a Master table with almost 40,000 records.
>> >> >> >> > The
>> >> >> > Details
>> >> >> >> > table contains 50,000 records. My query Inner Joins these two
>> > table
>> >> > and
>> >> >> >> > the
>> >> >> >> > query optimizer chooses Hash Join algorithm to perform the
> query.
>> >> >> >> > Obviously it will not be a good idea to try to change the plan
> to
>> > a
>> >> >> > Nested
>> >> >> >> > Loop because the number of rows in tables are large and close
> to
>> >> >> >> > each
>> >> >> >> > other.
>> >> >> >> > Therefore Merge Join will (probably) be the best algorithm. I
>> >> >> >> > must
>> >> >> > create
>> >> >> >> > an
>> >> >> >> > index on Details table beginning with FK column and including
>> > other
>> >> >> >> > columns
>> >> >> >> > to cover the query.
>> >> >> >> > The problem is that sometimes the number of required columns
> are
>> >> >> >> > more
>> >> >> > that
>> >> >> >> > allowable quantity or the length of index exceeds 900 bytes.
>> >> >> >> > Should I convince my boss to be satisfy with Hash Join or
> there's
>> > a
>> >> >> >> > solution?
>> >> >> >> > Any help will be greatly appreciated.
>> >> >> >> > Leila
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
