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 :

Expand|Select|Wrap|Line Numbers

 SELECT table_data.name, table_data.amount

FROM table_data

WHERE yr = 2009

But when I use the following subquery it is slower :

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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!

Faster search

re: Faster search

re: Faster search

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about