Help with query optimization

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...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that[vbcol=seagreen]
>|||<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 th
e
> 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 othe
r.
> Therefore Merge Join will (probably) be the best algorithm. I must create
an
> index on Details table beginning with FK column and including other column
s
> 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...
> not
> Details
> Nested
> create
> that
>|||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...[vbcol=seagreen]
>
> "Leila" wrote:
>
Details[vbcol=seagreen]
the[vbcol=seagreen]
Nested[vbcol=seagreen]
other.[vbcol=seagreen]
create an[vbcol=seagreen]
columns[vbcol=seagreen]
that[vbcol=seagreen]|||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...
a[vbcol=seagreen]
table[vbcol=seagreen]
included[vbcol=seagreen]
does[vbcol=seagreen]
and[vbcol=seagreen]
>|||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...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that[vbcol=seagreen]
>|||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...
> uses...
> Details
> the
> Nested
> other.
> create an
> columns
> that
>
>

Help with query optimization

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
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 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[vbcol=seagreen]
> 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...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that
>
|||<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...
> not
> Details
> Nested
> create
> that
>
|||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...[vbcol=seagreen]
>
> "Leila" wrote:
Details[vbcol=seagreen]
the[vbcol=seagreen]
Nested[vbcol=seagreen]
other.[vbcol=seagreen]
create an[vbcol=seagreen]
columns[vbcol=seagreen]
that[vbcol=seagreen]
|||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...[vbcol=seagreen]
> 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...
a[vbcol=seagreen]
table[vbcol=seagreen]
included[vbcol=seagreen]
does[vbcol=seagreen]
and
>
|||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?
>
> --
> ----
--[vbcol=seagreen]
> 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...
Details[vbcol=seagreen]
Nested[vbcol=seagreen]
create[vbcol=seagreen]
that
>
|||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...
> uses...
> Details
> the
> Nested
> other.
> create an
> columns
> that
>
>

Help with query optimization

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
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Help with query

I'm not sure if this is the right forum but here goes:

I want to make a query that selects data from multiple tables and joins it all together. I have that but what i want to do is only select the data I need. at the moment it is returning columns that are not necessary. I'm trying to do something like this:

Code Snippet

SELECT
sysdba.OPPORTUNITY.OPPORTUNITYID AS OPPID
FROM sysdba.OPPORTUNITY
INNER JOIN sysdba.C_OPPTYINFO
ON sysdba.OPPORTUNITY.OPPORTUNITYID = sysdba.C_OPPTYINFO.OPPORTUNITYID

For some reason the Inner Join is not joining the two tables. Any one have any suggestions?

Thanks in advance for the help.

Nothing wrong with your query, the tables are properly joined -IF both have a column [OpportunityID].

However, are you sure that there is data in both tables with the exact same [OpportunityID]?

|||The limited information you gave makes it hard for us to answer your questions: Perhaps the OppurtunityId is not the only key needed to identify the matching rows in both tables ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with Outer Joins

Hi. I'm using Microsoft Query to create a query for Excel 2003. I'm
attempting to create 3 Left Outer Joins, but upon doing so I receive the
error message "Query Can Have Only One Outer Join" as described here...
http://support.microsoft.com/defaul...14027#appliesto
After reading this article, I am under the impression that multiple Outer
Joins CAN indeed be created, only you must write the SQL code directly in
order to create them. That's were I'm lost. I'm not sure how to write the
necessary SQL code in order to join the 3 tables. Could someone please
assist me with the necessary code?
My tables are: tblOrder, Customer, tblSQFTByStation, and tblOrderDetail
tblOrder should be joined to the other 3 tables as follows...
FROM {oj `H:\Application_be`.tblOrder tblOrder LEFT OUTER JOIN
`H:\Application_be`.tblSQFTByStation tblSQFTByStation ON tblOrder.OrderID =
tblSQFTByStation.OrderID}
FROM {oj `H:\Application_be`.tblOrder tblOrder LEFT OUTER JOIN
`H:\Application_be`.Customer Customer ON tblOrder.CustomerID =
Customer.CustomerID}
FROM {oj `H:\Application_be`.tblOrder tblOrder LEFT OUTER JOIN
`H:\Application_be`.tblOrderDetail tblOrderDetail ON tblOrder.OrderID =
tblOrderDetail.OrderID}What you might be missing is that each join is not from 1 source to another,
it is from what you have so far to anothersource...
So (THis is probably wrong syntactically cause I don't know Exce
lsyntax...)tr this structure.
FROM oj `H:\Application_be`.tblOrder tblOrder
LEFT OUTER JOIN `H:\Application_be`.tblSQFTByStation tblSQFTByStation
ON tblOrder.OrderID = tblSQFTByStation.OrderID
LEFT OUTER JOIN `H:\Application_be`.Customer Customer
ON tblOrder.CustomerID = Customer.CustomerID
LEFT OUTER JOIN `H:\Application_be`.tblOrderDetail tblOrderDetail
ON tblOrder.OrderID = tblOrderDetail.OrderID
"MrMike" wrote:

> Hi. I'm using Microsoft Query to create a query for Excel 2003. I'm
> attempting to create 3 Left Outer Joins, but upon doing so I receive the
> error message "Query Can Have Only One Outer Join" as described here...
> http://support.microsoft.com/defaul...14027#appliesto
> After reading this article, I am under the impression that multiple Outer
> Joins CAN indeed be created, only you must write the SQL code directly in
> order to create them. That's were I'm lost. I'm not sure how to write th
e
> necessary SQL code in order to join the 3 tables. Could someone please
> assist me with the necessary code?
> My tables are: tblOrder, Customer, tblSQFTByStation, and tblOrderDetail
> tblOrder should be joined to the other 3 tables as follows...
> FROM {oj `H:\Application_be`.tblOrder tblOrder LEFT OUTER JOIN
> `H:\Application_be`.tblSQFTByStation tblSQFTByStation ON tblOrder.OrderID
=
> tblSQFTByStation.OrderID}
> FROM {oj `H:\Application_be`.tblOrder tblOrder LEFT OUTER JOIN
> `H:\Application_be`.Customer Customer ON tblOrder.CustomerID =
> Customer.CustomerID}
> FROM {oj `H:\Application_be`.tblOrder tblOrder LEFT OUTER JOIN
> `H:\Application_be`.tblOrderDetail tblOrderDetail ON tblOrder.OrderID =
> tblOrderDetail.OrderID}
>|||Thanks!
"CBretana" wrote:
> What you might be missing is that each join is not from 1 source to anothe
r,
> it is from what you have so far to anothersource...
> So (THis is probably wrong syntactically cause I don't know Exce
> lsyntax...)tr this structure.
> FROM oj `H:\Application_be`.tblOrder tblOrder
> LEFT OUTER JOIN `H:\Application_be`.tblSQFTByStation tblSQFTByStation
> ON tblOrder.OrderID = tblSQFTByStation.OrderID
> LEFT OUTER JOIN `H:\Application_be`.Customer Customer
> ON tblOrder.CustomerID = Customer.CustomerID
> LEFT OUTER JOIN `H:\Application_be`.tblOrderDetail tblOrderDetail
> ON tblOrder.OrderID = tblOrderDetail.OrderID
>
> "MrMike" wrote:
>

Help with nested inner joins

Hi,
I want to find some people in my SQLServer 2000 database. It's a quite large
database, with approx 200 tables.
Together with the person, I want some information attached to him. However,
this information is in another table that can be reached via some other
tables.
My question is:
How do I most efficiently extract this information? Is inner joins a good
option or is there a better way. If I need information from table1 and table
5, is this a good idea?
SELECT table1.ID, table5.info
FROM table1
INNER JOIN table2 ON table1.xxx = table2.xxx
INNER JOIN table3 ON table2.xxx = table3.xxx
INNER JOIN table4 ON table3.xxx = table4.xxx
INNER JOIN table5 ON table4.xxx = table5.xxx
Thanks,
Mats-LennartWithout seeing DDL, I can only go on assumptions...
I am assuming that the only logical way to connect tabel1 to table5 is via
tables 2, 3, and 4. Based on this, I believe the SQL below is the only way
to get the data you want.
If you post DDL (table creates, primary and foreign keys) for the tables
involved, folks may be able to explain another way to do it, or possibly
changes to your database structure.
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:e1rQtquNGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to find some people in my SQLServer 2000 database. It's a quite
large
> database, with approx 200 tables.
> Together with the person, I want some information attached to him.
However,
> this information is in another table that can be reached via some other
> tables.
> My question is:
> How do I most efficiently extract this information? Is inner joins a good
> option or is there a better way. If I need information from table1 and
table
> 5, is this a good idea?
> SELECT table1.ID, table5.info
> FROM table1
> INNER JOIN table2 ON table1.xxx = table2.xxx
> INNER JOIN table3 ON table2.xxx = table3.xxx
> INNER JOIN table4 ON table3.xxx = table4.xxx
> INNER JOIN table5 ON table4.xxx = table5.xxx
> Thanks,
> Mats-Lennart
>

Help with multiple Left Joins

Hi All,
this is my first time posting here as i cannot find the answer myself.
I have couple tables i want to join and i can't seem to get it right. I
have the following tables:
Part: (Part ID), PartDescription
Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
I want a query of the Available qty >0 for every part we have. When i
do a query like this, i get 5363 records.
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART left outer JOIN
dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
Then i want to add a column for this query, the inventory_Trans.Qty
that has type =O. I tried the query below and it doesn't
work...obviously ican't inner join again from PART_WAREHOUSE as it
does the left join based on that table, so this wouldn't work:
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART
left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_IS
left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
(INVENTORY_TRANS.TYPE='O')
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
I tried using this, but i am not familiar with this syntax and i am
getting errors.
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1, PART p2
LEFT JOIN
dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN
dbo.INVENTORY_TRANS as i on p2.ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
p1.unit_material_cost
Order by p1.warehouse_Id
so i am out of ideas. Can anyone enlighten me about how to do this:'
thank you so much in advance.Although this probably isn't the answer that you are looking for, but I'm
wondering why you are using the GROUP BY clause in your query? You typically
use GROUP BY when using an aggregate function in the SELECT statement, such
as COUNT. Try running the second and third queries without the GROUP BY
clause.
Try
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN dbo.INVENTORY_TRANS as i on w.ID = i.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
Order by p1.warehouse_Id
"lytung@.gmail.com" wrote:

> Hi All,
> this is my first time posting here as i cannot find the answer myself.
> I have couple tables i want to join and i can't seem to get it right. I
> have the following tables:
> Part: (Part ID), PartDescription
> Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
> Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
> I want a query of the Available qty >0 for every part we have. When i
> do a query like this, i get 5363 records.
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
>
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART
> left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_IS
> left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
> (INVENTORY_TRANS.TYPE='O')
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
> I tried using this, but i am not familiar with this syntax and i am
> getting errors.
> SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
> w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
> FROM PART p1, PART p2
> LEFT JOIN
> dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
> LEFT JOIN
> dbo.INVENTORY_TRANS as i on p2.ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
> group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
> p1.unit_material_cost
> Order by p1.warehouse_Id
>
> so i am out of ideas. Can anyone enlighten me about how to do this:'
> thank you so much in advance.
>|||no that gave me an error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'warehouse_Id'.
But even if that wo rked the logic doens't make sense.
I want the second query to be based on the first query. Maybe its not
about doing two joins but what i probably need is a transaction query.
First i need this:
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
where ( w.AVAILABLE_QTY > 0)
Then i need the i.type='O' (from inventory_trans) based on those
results. I hope this make sense!|||Hi
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
is it possible that part with given ID doesn't belong to a part_warehouse?
in other words can you get null as warehouse_id in above query?
the second thing - group by clause here is really not necessary

> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
how about this?
SELECT pw.WAREHOUSE_ID, p.ID, p.DESCRIPTION, p.UNIT_MATERIAL_COST,
pw.AVAILABLE_QTY
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
WHERE (pw.AVAILABLE_QTY > 0)
AND (itr.TYPE='O')
HTH
Peter|||Hi Peter,
thanks for replying. The query you gave me ended up with too many
records. You are right, i dont need the group by statement.
Part_Warehouse has 2 Primary Keys: Part_ID, and WAREHOUSE_ID
you skipped out the PART_WAREHOUSE join to PART. I guess for this join
it doesn't have to be a left join, but it has to be joined. The second
join has to be left, which you did...
I am getting with mixing the joins. What is the general rule
of multiple joins? does the second join depend on the previous join? or
can they be independent?|||use parentheses to prioritize joins. the outer table is joined to result of
join in parentheses.
can you show the ddl of these tables and some sample data and describe
result you would like to obtain?
part_warehouse is a table that relates parts and warehouses?
> you skipped out the PART_WAREHOUSE join to PART. I guess for this join
> it doesn't have to be a left join, but it has to be joined. The second
> join has to be left, which you did...
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
no, I left joined PART to the result of inner join between PART_WAREHOUSE
and INVENTORY_TRANS.
again, do you have PARTs without WAREHOUSEs?
peter

Help with JOINS in Stored Procedures

I am making a stored procedure, and I need to set some variables based on the results of a JOIN statement.

If I something like:

SELECT table.field, table.field2 FROM table INNER JOIN table2 ON table.field = string

How could I get the results and set them in variables?

Any help would be greatly appreciated.

GKC

Are you trying to do something like this?

CREATE PROCEDURE dbo.TestProcedure
(
@.SomeString varchar(50)
)
AS
SET NOCOUNT ON
SELECT t1.Field1, t1.Field2
FROM Table AS t1
INNER JOIN Table2 AS t2 ON t2.Field = t1.Field1
WHERE t1.Field1 = @.SomeString

HTH,
Ryan

|||No, basically I want to do this:

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

And take the results and put set them in some variables|||

DECLARE @.f1 ...
DECLARE @.f2 ...
DECLARE @.f3 ...

SELECT @.f1=field1, @.f2=field2, @.f3=field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

|||

GENIUS! So simple, I never thought!

Thank you!

help with joins

Hi all,

I have two tables which look like the following

Table A

ID Name

1 ABC

2 XYZ

3 PQR

Table B

Comments DateTime

ABC comments hi 10/10/06

NewComments ABC are 1/1/07

XYZ my comments 12/12/06

I want the output of the above to look like this: Left join on table A and recent most date

Name Date

ABC 1/1/07

XYZ 12/12/06

PQR

There is no column in common for these tables to join. So i am running a cursor through all names in table A

cursor for select name from table A

I will find each name in comments section and get recent most date with order by date desc. But this doesnt give the row 'PQR'.

Is there a way to left join these two tables.

Thanks

Maybe you want something like this?

create table #table_a
(
id int,
name varchar(10)
)

insert into #table_a values (1, 'ABC')
insert into #table_a values (1, 'XYZ')
insert into #table_a values (1, 'PQR')

create table #table_b
(
comments varchar(100),
timestamp datetime
)

insert into #table_b values ('ABC comments hi', '10/10/06')
insert into #table_b values ('NewComments ABC are', '01/01/2007')
insert into #table_b values ('XYZ my comments', '12/12/2006')

select
a.name,
max(b.timestamp)
from #table_a a
left outer join #table_b b
on b.comments like ('%' + a.name + '%')
group by
a.name
order by 1
;

|||

Hi Dan,

Thanks for the reply.

The Query works.But I have 5 more columns from table B to be added in the select list.

Even when add the same columns in the group by list,The Query return too many incorrect rows.

Any suggestion what i should do

Thanks

|||

I am not sure what you are trying to do with these additional columns.

Are they supposed to be from the record in Table_B that has the latest timestamp?

If so, we just need to use the existing query as a subquery. Like this:

select
tmp.*,
b.stuff1,
b.stuff2,
b.stuff3,
b.stuff4
from
(
select
a.name,
max(b.timestamp) max_timestamp
from #table_a a
left outer join #table_b b
on b.comments like ('%' + a.name + '%')
group by
a.name
) tmp
left outer join #table_b b
on b.timestamp = tmp.max_timestamp and
b.comments like ('%' + tmp.name + '%')
order by 1
;

|||

Thanks a lot Dan.Works perfectly fine for me.

Thanks

Help with Joins

Hi All,
I'm trying to write a query that will return the number of products sold,
and the total value of each products sales.
Heres what I have so far:
SELECT COUNT(cartrows.idProduct) AS QtySold, cartrows.idProduct AS
idProduct
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct
ORDER BY cartrows.idProduct
This works Ok, but I only want to return orders where the status is either
1, 2 or 7. What I came up with is below, which returns one line per
idProduct, per OrderStatus:
SELECT TOP 100 PERCENT COUNT(cartrows.idProduct) AS QtySold,
cartrows.idProduct AS idProduct,
carthead.orderStatus
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct, carthead.orderStatus
HAVING (carthead.orderStatus IN ('1', '2', '7'))
ORDER BY cartrows.idProduct
I guess this is the result I expect from this, I'm just not sure what I need
to do so I only get one line per idProduct, with the qty sold, only from
orders with an orderStatus of 1, 2 or 7.
Any help will be much appreciated. I've include table design statements
below.
Thanks!
Simon.
CREATE TABLE [carthead] (
[idOrder] [int] IDENTITY (1, 1) NOT NULL ,
[idCust] [int] NULL ,
[orderDate] [datetime] NULL ,
[orderDateInt] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[randomKey] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[subTotal] [float] NULL ,
[taxTotal] [float] NULL ,
[shipmentTotal] [float] NULL ,
[Total] [float] NULL ,
[shipmentMethod] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[lastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[customerCompany] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[phone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[address] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[city] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[zip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[shippingName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingZip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[paymentType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardExpMonth] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[cardExpYear] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardVerify] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[generalComments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[orderStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[auditInfo] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[storeComments] [text] COLLATE Latin1_General_CI_AS NULL ,
[storeCommentsPriv] [text] COLLATE Latin1_General_CI_AS NULL ,
[adjustAmount] [float] NULL ,
[adjustReason] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[discCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[discPerc] [float] NULL ,
[discTotal] [float] NULL ,
[shippingPhone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[handlingFeeTotal] [float] NULL ,
[idAffiliate] [int] NULL ,
[commPerc] [float] NULL ,
[otherFeeTotal] [float] NULL ,
[backOrder] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[idOrder]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [cartrows] (
[idCartRow] [int] IDENTITY (1, 1) NOT NULL ,
[idOrder] [int] NULL ,
[idProduct] [int] NULL ,
[sku] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[quantity] [int] NULL ,
[unitPrice] [float] NULL ,
[unitWeight] [float] NULL ,
[description] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[downloadCount] [int] NULL ,
[downloadDate] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[idDiscProd] [int] NULL ,
[discAmt] [float] NULL ,
PRIMARY KEY CLUSTERED
(
[idCartRow]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GODDL, great! Thanks!
Move the condition to the WHERE clause (untested, since you haven't provided
any sample data):
select count(cartrows.idProduct) as QtySold
,cartrows.idProduct as idProduct
from cartrows
inner join carthead
on cartrows.idOrder = carthead.idOrder
where (carthead.orderStatus in ('1', '2', '7'))
group by cartrows.idProduct
order by cartrows.idProduct
As I see it you want to restrict the result before the rows are grouped -
this is what WHERE does. The HAVING clause restricts results *after* the row
s
have been grouped.
ML
http://milambda.blogspot.com/|||Thank you for your reply - Especially the explanation of WHERE Vs HAVING.
Simon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0C331587-2374-4C79-A6BD-12F590E0FBC4@.microsoft.com...
> DDL, great! Thanks!
> Move the condition to the WHERE clause (untested, since you haven't
> provided
> any sample data):
> select count(cartrows.idProduct) as QtySold
> ,cartrows.idProduct as idProduct
> from cartrows
> inner join carthead
> on cartrows.idOrder = carthead.idOrder
> where (carthead.orderStatus in ('1', '2', '7'))
> group by cartrows.idProduct
> order by cartrows.idProduct
> As I see it you want to restrict the result before the rows are grouped -
> this is what WHERE does. The HAVING clause restricts results *after* the
> rows
> have been grouped.
>
> ML
> --
> http://milambda.blogspot.com/|||NP. Just remember which NG works for you. ;)
ML
http://milambda.blogspot.com/