Joins and inices

I can't understand this behavior:
I'm doing a join on a large table, and I created a
clustered index on the column in the join condition.
I would expect that this should enhance performance,
because of the merge join advantage.
It didn't perform well.
I then tried making that index non-clustered, and doing
a clustered index on an identity column (as a primary
key).
It's weird, but the join performs far better this way.
The only way I can explain this is that the non-clustered
index keeps the values of the join column in fewer pages,
and therefore closer together on disk (fewer reads).
Then again, there must still be the additional seek to
get to the rest of the "data" that row contains.
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
If anyone has any experience with this, or suggestions,
I'd be really grateful for some help.
Thanks in advance,
Andrew.It would be easier to offer ideas if you posted DDL and ideally insert
statements to load some sample data...
this doesn't answer all of your questions but as an FYI...
<<
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
the NC index actually keeps the clustering key as part of it's own key
information at the leaf level of the NC index. So... you're actually
changing the contents of the NC index if you get rid of the clustering key.
Also, NC index is stored entirely differently if there is no clustered index
at all...
--
Brian
"Andrew" <a@.b.com> wrote in message
news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
> I can't understand this behavior:
> I'm doing a join on a large table, and I created a
> clustered index on the column in the join condition.
> I would expect that this should enhance performance,
> because of the merge join advantage.
> It didn't perform well.
> I then tried making that index non-clustered, and doing
> a clustered index on an identity column (as a primary
> key).
> It's weird, but the join performs far better this way.
> The only way I can explain this is that the non-clustered
> index keeps the values of the join column in fewer pages,
> and therefore closer together on disk (fewer reads).
> Then again, there must still be the additional seek to
> get to the rest of the "data" that row contains.
> Even stranger, if the clustered index on the identity col-
> umn (PK) is not there, the join doesn't perform well also.
> If anyone has any experience with this, or suggestions,
> I'd be really grateful for some help.
> Thanks in advance,
> Andrew.|||Still...
><<
>Even stranger, if the clustered index on the identity
col-
>umn (PK) is not there, the join doesn't perform well
also.
>the NC index actually keeps the clustering key as part
of it's own key
>information at the leaf level of the NC index. So...
you're actually
>changing the contents of the NC index if you get rid of
the clustering key.
>Also, NC index is stored entirely differently if there
is no clustered index
>at all...
>--
I realize the NC index keeps the clustering key as part
of it's own key, but I had understood that SQL server
keeps a RID (row identifier) internally when there is no
clustered index... so rather thank keeping the clustered
index in the leaf, the RID is kept.
Now, if accessing that RID is slow, I can understand, but
I'd imagined it as a kind of internal indexed identity
of its own. I guess this doesn't make sense, though.
As a follow up, though, is there no use for a NC index
without a clustered index when you want to access columns
outside of the NC indexed column?
Also, can a column be both a clustered and a non-clustered
index (would this help on join performance to access those
other columns?).
Thanks again,
Andrew
>Brian
>
>"Andrew" <a@.b.com> wrote in message
>news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
>> I can't understand this behavior:
>> I'm doing a join on a large table, and I created a
>> clustered index on the column in the join condition.
>> I would expect that this should enhance performance,
>> because of the merge join advantage.
>> It didn't perform well.
>> I then tried making that index non-clustered, and doing
>> a clustered index on an identity column (as a primary
>> key).
>> It's weird, but the join performs far better this way.
>> The only way I can explain this is that the non-
clustered
>> index keeps the values of the join column in fewer
pages,
>> and therefore closer together on disk (fewer reads).
>> Then again, there must still be the additional seek to
>> get to the rest of the "data" that row contains.
>> Even stranger, if the clustered index on the identity
col-
>> umn (PK) is not there, the join doesn't perform well
also.
>> If anyone has any experience with this, or suggestions,
>> I'd be really grateful for some help.
>> Thanks in advance,
>> Andrew.
>
>.
>

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.
Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>
|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...[color=darkblue]
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
VS.State_Cd[color=darkblue]
> =
HT.State_cd
>
|||But you're joining on the same columns... Both joins are VS.State_Cd =
HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message[color=darkblue]
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
on
> VS.State_Cd
> HT.State_cd
>
|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>
|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
.......
)
Table2 ( Type_ID int Primary Key
...............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd =
> HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> message
> on
>
|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message[vbcol=seagreen]
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
based[vbcol=seagreen]
..[vbcol=seagreen]
index.
>

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
VS.State_Cd
> =
HT.State_cd
>|||But you're joining on the same columns... Both joins are VS.State_Cd =
HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
on
> VS.State_Cd
> HT.State_cd
>|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =
HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=
> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
......
)
Table2 ( Type_ID int Primary Key
..............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd =
> HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> message
> on
>|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
based
.
index.
>sql

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd = HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > Do anybody know how to get data from very small and huge table based on
> > common column(ex : Stae_Cd).Index on common column is not useful.
> > Very small table has lookup table (static data). Please suggest .
> >
> >
> >
> > SELECT DISTINCT
> >
> > VS1.coverage_id,
> > HT.Col_id,
> > VS2.Col3
> >
> > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
VS.State_Cd
> => > HT.State_cd and
> >
> > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd =HT.State_cd
> >
> > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> >
> >
> > Thanks in advance.
> >
> >
>|||But you're joining on the same columns... Both joins are VS.State_Cd =HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > Why are you joining to your VerySmall_Table1 twice?
> >
> >
> > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > Do anybody know how to get data from very small and huge table based
on
> > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > Very small table has lookup table (static data). Please suggest .
> > >
> > >
> > >
> > > SELECT DISTINCT
> > >
> > > VS1.coverage_id,
> > > HT.Col_id,
> > > VS2.Col3
> > >
> > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> VS.State_Cd
> > => > > HT.State_cd and
> > >
> > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => HT.State_cd
> > >
> > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> > >
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
.......
)
Table2 ( Type_ID int Primary Key
...............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd => HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > Table design is like that.Two tables has same column with other info.
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > > Why are you joining to your VerySmall_Table1 twice?
> > >
> > >
> > > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > > Do anybody know how to get data from very small and huge table based
> on
> > > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > > Very small table has lookup table (static data). Please suggest .
> > > >
> > > >
> > > >
> > > > SELECT DISTINCT
> > > >
> > > > VS1.coverage_id,
> > > > HT.Col_id,
> > > > VS2.Col3
> > > >
> > > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> > VS.State_Cd
> > > => > > > HT.State_cd and
> > > >
> > > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => > HT.State_cd
> > > >
> > > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> > > >
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >
>|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> > But you're joining on the same columns... Both joins are VS.State_Cd => > HT.State_cd. Why not just join once?
> >
> >
> > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > > Table design is like that.Two tables has same column with other info.
> > > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> > message
> > > news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > > > Why are you joining to your VerySmall_Table1 twice?
> > > >
> > > >
> > > > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > > > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > > > Do anybody know how to get data from very small and huge table
based
> > on
> > > > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > > > Very small table has lookup table (static data). Please suggest
.
> > > > >
> > > > >
> > > > >
> > > > > SELECT DISTINCT
> > > > >
> > > > > VS1.coverage_id,
> > > > > HT.Col_id,
> > > > > VS2.Col3
> > > > >
> > > > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> > > VS.State_Cd
> > > > => > > > > HT.State_cd and
> > > > >
> > > > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => > > HT.State_cd
> > > > >
> > > > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered
index.
> > > > >
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Join takes too long to execute

This join takes 6 seconds to execute, and that's too long. There are several
hundred thousand records in all tables with and index on the ID field of eac
h
table. The plan is to have this in a SP with clients calling it repeatedly s
o
it needs to be fast.
I'm wondering if i can permanently related these tables outside of the SP?
Then the join wouldn't be necessary and i could just do a SELECT
..where...etc?
select distinct m.* from main m
left join sym2 s on s.id = m.id
left join cat2 c on c.id = m.id
left join servid2 i on i.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
Thanks,
Don
SQL 2000Hi
You may want to look at the query plan for this, it may help if you use
dynamic SQL or a union.
e.g. (untested!)
select m.*
from main m
join sym2 s on s.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
UNION
select m.*
from main m
join cat2 c on c.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
select m.*
from main m
join servid2 i on i.id = m.id
where i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
For production code you should not use SELECT *
John
"DonSQL2222" wrote:

> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
> Thanks,
> Don
> SQL 2000|||Don,
Does this query really execute? I always thought you could only call
UDF's (in this case iter_charlist_to_table) if you mention the UDF
owner.
How many rows does the table valued UDF return in these three cases? If
it returns more than say 1000 rows, then you should try to remove it.
You only select rows from table main. If main is a regular table with a
primary key, then you can move the joins to EXISTS clauses, remove the
DISTINCT keyword, and change the outer joins to inner joins.
For example:
SELECT *
FROM main m
WHERE EXISTS (
SELECT 1
FROM sym2 s
WHERE s.id=m.id
AND s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM cat2 c
WHERE c.id=m.id
AND c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM servid2 i
WHERE i.id = m.id
AND i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
)
If the table valued UDF is in fact the main problem, then you could
consider rewriting the query to this:
SELECT DISTINCT *
FROM main m
INNER JOIN (
SELECT id FROM sym2 s
WHERE s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
UNION ALL
SELECT id FROM cat2 c
WHERE c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
UNION ALL
SELECT id FROM servid2 i
WHERE i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
) AS T1 ON T1.id = m.id
Hope this helps,
Gert-Jan
DonSQL2222 wrote:
> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFA
ULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT
) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFA
ULT))
> Thanks,
> Don
> SQL 2000