Best Query

[vbkris]

Hi guys,

the query topic may not be helpful, however this is my problem.

Table1: Master Table
Table2: Child table.

No relationships defined between these two tables .

The child table has around 1 million records.

I have a simple request. Find out all the values in the master table that are available in the child table.

Normally this is a simple join. However since there are no relationships its very costly (high IO).

any other way to do this without joins?

another question by the way, any FK is an implicit index right???

Known is handfull, Unknown is worldfull

 

[Crowley16]

if you apply the right indexes, then you can perform fast joins without having to declare any relationships.

--------------------
Procrastinate Now!

 

[vbkris]

hey crowley,

i have a problem there. the child table is too dynamic and has too many columns (around 70!).
creating an index is not an option that is immediately open for me
...

Known is handfull, Unknown is worldfull

 

[Crowley16]

eh, you don't have to index the entire table, just index the column you're using to join on...

if you don't apply indexes, then no matter what you do, you'll get slow search results...

and no, foreign keys don't automatically get indexed, you have to set it yourself...

--------------------
Procrastinate Now!

 

[vbkris]

>>eh, you don't have to index the entire table
already this table has a lot of indexes. Plus since this is the maximum used table (dont control the design), i have no say there.
i just wanted to find if there was some alternate query / way that i was missing out on. looks like there is no other way but indexes.

>>and no, foreign keys don't automatically get indexed
arent related table joins faster?

Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table.
An index allows Microsoft® SQL Server™ 2000 to find related data in the foreign key table quickly.

However, creating this index is not a requirement.

Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables,

but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria

this qoute from the BOL is pretty confusing. first it states that an index is used. Then it states that the index is NOT mandatory. finally it goes on to say that the query is "optimised" better for a join if a relationship is there.

any ideas on this???

Known is handfull, Unknown is worldfull

 

[Crowley16]

best way would be to just time the operations with each setup...

--------------------
Procrastinate Now!

 

[ESquared]

Foreign keys can only be defined against columns which are primary or unique. So the primary key table must have an index. But the foreign key table index is optional.

The last bold line is something about best practice our about the general assumptions you as a developer might make about tables, but in practice it's not always true. At least you know that the primary key table is indexed on the column...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

thanks guys,

that helped a lot...

Known is handfull, Unknown is worldfull

 

[ESquared]

The way I said that was misleading.

Foreign keys can only be [red]defined against[/red] related to primary key columns which are primary or unique.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

>>Foreign keys can only be related to primary key columns which are primary or unique.

dont find any difference between the both

Known is handfull, Unknown is worldfull

 

[ESquared]

The foreign key column itself need not have any index or constraint on it.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

ah,

got it...

Known is handfull, Unknown is worldfull