By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Faster search

P: 1
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
  1. SELECT table_data.name, table_data.amount
  2. FROM table_data
  3. WHERE yr = 2009
But when I use the following subquery it is slower :
Expand|Select|Wrap|Line Numbers
  1. SELECT table_data.name, table_data.amount
  2. FROM table_data
  3. 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.
Jan 5 '09 #1
Share this Question
Share on Google+
2 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Oreber.

Try to use join instead of subquery:
Expand|Select|Wrap|Line Numbers
  1. SELECT table_data.name, table_data.amount
  2. FROM table_data INNER JOIN parameter_table
  3.   ON table_data.yr=parameter_table.yr_today;
Just out of curiosity - what is this table combination supposed to do?

Regards,
Fish.
Jan 5 '09 #2

NeoPa
Expert Mod 15k+
P: 31,489
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!
Jan 6 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.