CODEWITHSUNDEEP
sqlms-access
Desert Spider
Desert Spider

Reputation: 778

MS-Access Slow Query

I have a query that is pulling information from one table. That table is rather large at 1.8 Million rows and growing by week. The query takes quite a while to run and is problematic when pulling multiple times. Is there any process that may speed up a query in a database with this many or more rows. I have another one with around 5 Million rows... The query is rather basic using a prompt to pull the rows relevant to the site number, and a prompt for between dates.

Arrival_ID criteria = [Select Arrival ID]
Week criteria = Between[Select week begin:] And [Select week end:]

Any help or direction pointing would be greatly appreciated.

Upvotes: 4

Views: 1835

Answers (2)

ljvincent
ljvincent

Reputation: 43

I agree with Kieren Johnstone - can you store the data in SQL and then use access to run the queries?

Do double check the indexes.

When you compact/repair - do it twice - make it a habit. The second time clears up any issues set aside from the first one.

Upvotes: -2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

Indexes on the columns Arrival_ID and Week might help.

Unless you're selecting a lot of columns from a very wide table, you should get fairly quick performance from Access on 1.8 million rows, as long as your indexes are selective.

Upvotes: 5

Related Questions