Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2018, 01:50 AM   #31
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,238
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Why indexing is important for good performance

Quote:
Using filters requires bringing the entire server side table or query recordset down from the server to memory on the local PC where Access manages the filtering.
I think this is where the access templates lead newbies in the wrong direction. 9 time out of 10, the template forms are based on tables - and of course the subform construct relies on filtering when you change the mainform record.

Shame Access doesn't have a 'mode' property to allow users to make a choice - simplistically 'professional' - tables can only be viewed via query, forms/reports can only have a query recordsource, field names do not have spaces or use reserved words, etc, 'just messing' - do what you like, but expect plenty of issues along the way.

in professional mode the query builder could be improved to allow a greater variety of joins (although I suppose the argument would then be don't use the query builder)

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-10-2018, 02:31 AM   #32
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,366
Thanks: 112
Thanked 2,845 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Why indexing is important for good performance

Quote:
Originally Posted by CJ_London View Post
Shame Access doesn't have a 'mode' property to allow users to make a choice - simplistically 'professional' - tables can only be viewed via query, forms/reports can only have a query recordsource, field names do not have spaces or use reserved words, etc, 'just messing' - do what you like, but expect plenty of issues along the way.

in professional mode the query builder could be improved to allow a greater variety of joins (although I suppose the argument would then be don't use the query builder)
What a great idea. One for the Access User Voice?
Mind you, I expect chances of being implemented to be close to zero as that would indicate that the current system is flawed
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-10-2018, 03:02 AM   #33
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,937
Thanks: 433
Thanked 284 Times in 249 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Why indexing is important for good performance

Quote:
Using filters requires bringing the entire server side table or query recordset down from the server to memory on the local PC where Access manages the filtering
A point I took to heart when we migrated to SQL. However, the clients/users were used to clicking the “filtered” indicator in the record selector portion of my the split form (not my decision) to toggle from open records (about 600) to all records (18k, about 4 years of history) to make research easier. I had built different views on the server side just in case the performance became an issue, but much to my surprise (and their pleasure; they HATE change) the performance hit was almost imperceptible...even at our remotest location, Bahrain. Pretty impressive considering the server is in Naples, Italy.

As much as it bothers me that our application isn’t fully optimized, I am thankful that Access and SQL Server work so well together.

__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 06-10-2018, 09:35 AM   #34
jleach
Newly Registered User
 
jleach's Avatar
 
Join Date: Jan 2012
Location: New York, NY
Posts: 308
Thanks: 16
Thanked 70 Times in 70 Posts
jleach will become famous soon enough
Re: Why indexing is important for good performance

Quote:
Originally Posted by ridders View Post
What a great idea. One for the Access User Voice?
Mind you, I expect chances of being implemented to be close to zero as that would indicate that the current system is flawed
We fought for years to have them supply basic templates that followed some sort of best practices (which I don't know if they ever did, actually), let alone actually enforce anything within the program itself.

Nice thought, and I'd give it a vote, but I'd be less surprised by a talking penguin, I think, if they actually did it.
__________________
- Jack D. Leach

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Microsoft MVP 2012-2016
jleach is offline   Reply With Quote
Old 06-12-2018, 12:18 PM   #35
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Why indexing is important for good performance

Quote:
Originally Posted by CJ_London View Post
I think this is where the access templates lead newbies in the wrong direction. 9 time out of 10, the template forms are based on tables - and of course the subform construct relies on filtering when you change the mainform record.
Yep, there's a reason that in my current apps, the subforms always resolve to a single record based on the value in the parent form, rather than using the Parent/Child links.

When you're doing this on a table with 1.6 billion records, the normal filtering mechanic just isn't remotely acceptable.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 06-12-2018, 12:40 PM   #36
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,366
Thanks: 112
Thanked 2,845 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Why indexing is important for good performance

1.6 billion!?! US billion (10^9) or UK billion (10^12)
Either way that's a lot - presumably a SQL database as I'm sure it would be more than 2GB

In one of my apps I have a table of 2.6 million postcodes
Searching is obviously impossible using a combo box so to make it manageable, I break it down using 5 cascading combos, each of which are fast:



Do you have a different solution?
Attached Images
File Type: png PostcodeBuilder.PNG (85.3 KB, 96 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-12-2018, 03:43 PM   #37
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Why indexing is important for good performance

Yeah, 1.6 billion. American billion, thank God. And we add a couple million more every week. That table alone is somewhere on the far side of 1 TB in size.

Keep in mind that I work for one of the largest health insurance organizations in the US. My particular subset only handles Michigan, and we still have 4.5 million members in Michigan and 1.5 million more in other states. For the record, I actually use a view covering all claim lines* received in 2010-2012, 2012-2014, and 2014 to the current day (which is the 1.6 billion record table - the others are smaller).

*A claim line is a single action on an insurance claim. If you go to the doctor here, the claim is your visit, and the lines could include your service fee for walking in the door, 3 lines for different blood tests, a line for an x-ray, and a line for a treatment provided on-site. If you wind up in the hospital for something complicated and stay there for several weeks, the resulting claim could be THOUSANDS of lines. When I was hospitalized for two days for simple Cellulitis, my claim was still over 50 lines.

I assume your screenshot isn't the normal entry, but instead is used to find a postal code if you don't already know it. Given that constraint, I'd have probably done the exact same thing (except that I don't know how to add the Google Maps shot showing the location). Speed-wise, I don't think you'll find a faster solution than you used.

Another option I found on this very site (Paul Brown, maybe? Maybe Mr. Baldy?) was to have a combo box that doesn't actually pull a list until a certain number of characters are entered, in order to cut down on the recordset size. I'm not entirely sure how even that would perform against millions of records, though, and I really doubt it would match the performance of what you showed here.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 06-12-2018, 03:55 PM   #38
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,366
Thanks: 112
Thanked 2,845 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Why indexing is important for good performance

That's certainly a big file....
The postcodes BE file is 1.6GB with almost all of that in one table but it's only updated each quarter.

The normal postcode entry is a textbox.
The builder is indeed when you aren't sure what to enter.
Each combo is based on a separate table filtered for the previous selection which makes it very fast.

Allen Browne had a combo that behaved as you described. I tried the idea for this setup but found it utterly unusable
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-12-2018, 04:03 PM   #39
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Why indexing is important for good performance

We have a dedicated virtual server with something around 12 or 15 TB of space. That table and its archives are far and away the biggest tables we have, but they're also the core of everything my department does. We update it every Monday night, and it feeds something like 20 different in-house applications.

And it doesn't even include prescriptions! That's another table entirely.

(In a nutshell, my department tracks down over and under payments and gets them corrected, as well as dealing with the administrative nightmare that is Medicare and determining whether Medicare pays for something or we do. Last year we actually recovered $87 million that had been over- or incorrectly-paid.

I had a feeling that your postal code table would break that dynamic combo box.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 06-12-2018, 04:15 PM   #40
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,238
Thanks: 40
Thanked 3,645 Times in 3,515 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Why indexing is important for good performance

Quote:
Another option I found on this very site (Paul Brown, maybe? Maybe Mr. Baldy?) was to have a combo box that doesn't actually pull a list until a certain number of characters are entered,
its a technique I use - requirement is to enter 3 chars (or wait 1/2 second on the first 2) - and I don't use an initial * - I train the user to use it if required.

You can also enter multiple entries separated by a semi colon. i.e. an entry of smi;bro will return all names beginning with smi or bro.

simplistically/statistically even entering one character reduces the returned recordset by 96%

I have a client with circa 3m records - time to return a recordset is generally less than a second but is based on a simple search of one field or another.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-12-2018, 04:51 PM   #41
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,605
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Why indexing is important for good performance

Quote:
Originally Posted by ridders View Post
1.6 billion!?! US billion (10^9) or UK billion (10^12)
Bit behind the times there Colin. UK adopted the US terminology in 1975.
Galaxiom is offline   Reply With Quote
Old 06-12-2018, 11:58 PM   #42
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,366
Thanks: 112
Thanked 2,845 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Why indexing is important for good performance

I'd honestly never realised the UK government had swopped over back in the 1970s.
Must have been at the same time the Heath government imposed a three day week.
http://www.plainenglish.co.uk/campai...a-billion.html

However, I think a GAZILLION of people in the UK still think of a billion meaning 10^12
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-13-2018, 05:13 AM   #43
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,358
Thanks: 87
Thanked 1,643 Times in 1,525 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Why indexing is important for good performance

How many zeroes follow the 1 for that "gazillion" again? Is it bigger than a googleplex?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-13-2018, 06:06 AM   #44
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,366
Thanks: 112
Thanked 2,845 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Why indexing is important for good performance

Quote:
Originally Posted by The_Doc_Man View Post
How many zeroes follow the 1 for that "gazillion" again? Is it bigger than a googleplex?
There are a scintillion of zeroes or one less than in a googleplex
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-13-2018, 12:41 PM   #45
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,358
Thanks: 87
Thanked 1,643 Times in 1,525 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Why indexing is important for good performance

A truly scintillating answer, Colin!

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Important - Please help if you can! fat controller The Watercooler 10 08-06-2016 08:16 AM
please it's very important to my work tarek_ta2ta2 General 2 03-23-2009 05:00 AM
**important Message** Groundrush General 0 02-23-2004 04:32 AM
Important: Access 97 bug? StefanSch Forms 5 03-10-2003 08:38 PM
Say Good morning, Good afternoon! Sohaila Taravati Forms 4 02-22-2002 07:49 AM




All times are GMT -8. The time now is 08:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World