simple JOIN, huge table

[techsupport3977]

I have a huge table that has thousands of records. I only need to look at 3 fields from the table. I have another table that has 100 records with 1 field I need. I have a JOIN between the two tables with the field that has the same data. It is a standard JOIN that I have used in hundreds of small to medium sized tables. Since this is a huge table it takes too long to crank the data. Is there any advice on speeding up the massaging of the data so I am not ticking any one off.

 

[SkipVought]

How about posting your SQL?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PHV]

In each table create an index on the common field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

Thousands or records joining to 100 records is small beer for Jet. There must be something else slowing it down. Post your SQL and tell us which fields have indexes.

 

[jsteph]

An index will certainly help, and 'thousands' of records is not necessarily 'huge' for Access, but check the sql to see if you're possibly using criteria on the bigger table that might cause the nested join not to happen at all or to be executed in an inefficent manner--even nested index joins can be speeded up.

If the indexes alone don't help, your next step is to look into the JetShowPlan utility--you need to set a registry key to turn it on, but other than that it's simple. It'll pinpoint if you're using criteria that causes an inefficient execution plan, and you can adjust the sql accordingly, if it still fits within the desired results.
--Jim