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