422,027 Members | 1,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,027 IT Pros & Developers. It's quick & easy.

access speed in displaying records in form

P: 25
Duh questions perhaps but I wonder if anyone had a rough idea.
Would a stored Access query perform noticeably faster if it had to display 15 records (6 fields no calculations, no subqueries) on a form from a table with 300 records instead of 800.000?

Thanks
Oct 8 '17 #1

✓ answered by PianoMan64

It's going to depend on the criteria in the query that you have, if it has to crawl all 800,000 records to find it, or if you are simply going to read the first 15 records from the table without regard to order. If you're going to have a specific order to the display. that is where the time will be used to find them. If it is index and tuned correctly that will minimize the amount of time that it will take to locate the 15 records you're going to display on the screen.

Share this Question
Share on Google+
5 Replies


P: 25
Anyone?
I just wanted to know roughly, in your experience, if it is a matter of seconds, many seconds?
If anyone knows where I could get an access back end with that amount of random records for testing, i'll do it. Could not find one .
Thanks
Oct 10 '17 #2

Expert 100+
P: 374
It's going to depend on the criteria in the query that you have, if it has to crawl all 800,000 records to find it, or if you are simply going to read the first 15 records from the table without regard to order. If you're going to have a specific order to the display. that is where the time will be used to find them. If it is index and tuned correctly that will minimize the amount of time that it will take to locate the 15 records you're going to display on the screen.
Oct 11 '17 #3

NeoPa
Expert Mod 15k+
P: 30,549
As PianoMan says, it may or it may not. It certainly CAN make life very uncomfortable, taking minutes to process. On the other hand, if all is set up sensibly and filtered on sargable fieldsets, it can be almost as responsive as that for 300 records.

If you don't understand the word sargable then look it up. You won't regret it if you plan to do any amount of work in databases.
Oct 12 '17 #4

P: 25
Thanks.
to be more specific, these 15 records are inserted in a table one after the other(as a block of 15) and they are child records. They could be anywhere as a pack. So I suppose that if i search for 15 records where the fkID is x and the fkID is indexed, Access shouldn't be complaing that much? I wanted to have an idea because I might want to create an intermediate table that will never have a large amount of records. Don't want to go that route thought, I want to keep it simple if I can.
Thanks for that word NeoPa, have never come across it, so yes I am new to access and love to optimize as much as I can.
Oct 12 '17 #5

NeoPa
Expert Mod 15k+
P: 30,549
That would certainly be sargable and yes, that would give you very reasonable performance in any scenario I can think of. I suspect you'd run out of the ability to hold the records before the indexing would be severely stretched.

Obviously, the thing to do is to try it out now, but you can be comfortable that it will grow well.
Oct 15 '17 #6

Post your reply

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