Faster search

[Oreber]

HELP....

I am trying to read a table that has 598,865 records. The problem is when I try to access it by year. When I use the following query it is fast :

SELECT table_data.name, table_data.amount
FROM table_data
WHERE yr = 2009

But when I use the following subquery it is slower :

SELECT table_data.name, table_data.amount
FROM table_data
WHERE yr = (select yr_today from parameter_table)

NOTE : parameter_table is a table with 1 record and 2 fields : yr_today(number , in this case 2009) and yr_prev(number, in this case 2008).

Can anyone help me with the second query so it can be faster, because I don't like to hard code data in the queries.

[FishVal]

Hello, Oreber.

Try to use join instead of subquery:

SELECT table_data.name, table_data.amount
FROM table_data INNER JOIN parameter_table
  ON table_data.yr=parameter_table.yr_today;

Just out of curiosity - what is this table combination supposed to do?

Regards,
Fish.

[NeoPa]

In general, Access "gives up" trying to be clever with SQL as soon as it hits a sub-query.

Fish's INNER JOIN solution should sort you out though :)

Welcome to Bytes!