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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-03-2017, 05:09 AM   #1
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 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
Why indexing is important for good performance

Indexing is essential for the efficient retrieval of data from a db.

Some may be concerned about the additional disk space required for the index(es) - but disk space is cheap and they do not take up that much space. For small db's - a few '000 records per table and simple queries it is probably not noticeable but with more records and more complex queries and you will start to notice performance issues without indexing.

A simple rule of thumb is to open the table and look at the recordcount at the bottom. If it is not populated instantly, you need indexing because the time it takes to populate is about the time it takes to do a sequential search.

Think of the table as being a library of books all stored in a random order on shelves. (which is how a table stores it records, they are not stored in the order you enter them). You don't have an index so to find a particular book you go along the shelves until you find the one you want - sometimes you are lucky and it is on the first shelf, sometimes not and it is on the last shelf - and you may still need to go along all the shelves if looking for all books by a particular author.

Now the librarian realises this takes too long so they create an index - a piece of paper which contains say book title and the aisle and shelf the book is stored on maintaining in alphabetical order of the book title. They may create another one based on the author. Now you can scan down the piece of paper, find the book you want and go straight to the shelf. That is what an index is - a separate 'object' containing the value of a field maintained in order and a pointer to that record in the table.

To understand why indexing is so efficient, you need to understand how computers connect with the datasource - simplistically in the case of a disk it will 'read' a block of data from the disk (i.e. a page of the librarian index), not sure what it is these days but probably something like 4kb look for what is required and if it doesn't find it, read the next block. If it is reading blocks with whole records (for a sequential search) it might pick up say 100 records, but if indexed it might pick up 1000 (because indexes are smaller) so will find your record 10 time quicker - and with indexing algorithms it will have a better idea of what block to read next - in the analogy above, you look at the first page - books starting with A, but since you are looking for a book starting with Z, you know to go to the last page and work backwards.

Indexing does have a time overhead when a record is inserted, deleted or the indexed field changed because it needs to be updated to maintain the index order. But this more than pays dividends when you want to find that record again.

However there is no point in indexing for the sake of it, just those fields you are going to join on or regularly sort and/or filter on. There is also little point in indexing fields which have few distinct values (like Booleans) or contain a lot of nulls because the index itself will not be that efficient (although with Access you can set the index to ignore nulls). Go back to library analogy - how useful is it to have pages of 'Yes' followed by pages of 'No'?

Clearly the size of the index field will also have an impact - the larger the datatype, the fewer elements can be read in a single block. Longs (typically used for autonumber primary keys) are 4 bytes in length, dates (used for timestamps for example) are 8 bytes in length whilst text is 10 bytes plus the number of characters.

Users will still need to search on text (for a name for example), but performance can be improved by using a numeric link between a relatively short list of author names and the long list of books they have written. Primary key is in the author name table and foreign/family key is in the book table.

Using an initial * in a like comparison, prevents the use of indexing because indexing starts from the first character (think of the librarian index above) so should be avoided as much as possible - how many users would look for 'Smith' by entering 'ith'? This is also a good reason for storing names in two fields (firstname/lastname) rather than a single field so each can be indexed separately.

Sometimes it can't be avoided - but better to either train users to enter a * when required, or provide a button/option for begins with/contains to implement the initial * when required.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 06-20-2017 at 04:47 AM.
CJ_London is online now   Reply With Quote
The Following 6 Users Say Thank You to CJ_London For This Useful Post:
bcomb (01-26-2017), Catalina (01-06-2017), DiogoCuba (12-27-2018), Frothingslosh (01-04-2017), has_access (08-17-2018), isladogs (06-25-2017)
Old 01-04-2017, 04:43 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Why indexing is important for good performance

Definitely something anyone working with databases should be aware of.
__________________

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-20-2017, 01:42 AM   #3
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,998
Thanks: 443
Thanked 296 Times in 257 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Why indexing is important for good performance

Interesting points. I have always assumed that Access had some sort of behind the scenes optimizer that built secondary, tertiary, etc... indexes to improve performance so I never thought about it.

Should have known better...

NauticalGent is offline   Reply With Quote
Old 06-20-2017, 01:53 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 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

'fraid not.

But access provides a performance analyser which recommends improvements such as indexing.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 06-20-2017, 04:04 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,537
Thanks: 92
Thanked 1,678 Times in 1,558 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

CJ - you might wish to make ONE update. You surmised the size of an index block was "4mb" but it is 4kb. That is governed by factors external to Access; things like the "standard" Windows device driver and most common disk geometry in sectors per track. Other than that, great contribution.
__________________
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-20-2017, 04:09 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,931
Thanks: 114
Thanked 2,990 Times in 2,719 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 isladogs is a splendid one to behold
Re: Why indexing is important for good performance

Thanks CJ - Really well explained

Doc Man raised the only point I was going to make - I must be turning into a Doc clone ....
__________________
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
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-20-2017, 04:50 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 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

I've corrected the original post. Back in the day it was 4kb, I just assumed with bigger disks, more memory, 64bit v 8bit (as was) the block would have grown.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 06-20-2017, 05:02 AM   #8
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,998
Thanks: 443
Thanked 296 Times in 257 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Why indexing is important for good performance

Quote:
Originally Posted by ridders View Post
... - I must be turning into a Doc clone ....
Saints preserve us all...
NauticalGent is offline   Reply With Quote
Old 06-20-2017, 05:08 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,931
Thanks: 114
Thanked 2,990 Times in 2,719 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 isladogs is a splendid one to behold
Re: Why indexing is important for good performance

Also saw your post on another thread earlier today & thought you did a perfect DocMan tyoe response to that one ....

Still I reckon having one Doc Man is all we really need on this forum.
He can't be improved upon ...!
__________________
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
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-20-2017, 05:11 AM   #10
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,998
Thanks: 443
Thanked 296 Times in 257 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Why indexing is important for good performance

No...he is a verbal force to be reckoned with. For me to imply I could be his equal was quite egotistical of me!
NauticalGent is offline   Reply With Quote
Old 06-21-2017, 05:19 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,537
Thanks: 92
Thanked 1,678 Times in 1,558 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

Aw, shucks! You guys are too kind.
__________________
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-21-2017, 05:24 AM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,537
Thanks: 92
Thanked 1,678 Times in 1,558 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

CJ, about 64-bit Windows upping the buffer size: As we well know, MicroSoft, in their *ahem* infinite wisdom, didn't make a change to the maximum database size even for a jump to the 64-bit version of Access. 64-bit Excel can handle larger worksheets, but 64-bit Access cannot handle bigger files. I would hazard a guess that if a new version of Access ever breaks the 2 Gb file limitation, at that point we MIGHT see larger, better buffering.
__________________
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-21-2017, 05:50 AM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,328
Thanks: 40
Thanked 3,667 Times in 3,536 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

@Doc

could be, my own guess is that ACE will be retired and effectively replaced with a 'cut down' version of sql server express which has a 10gb limit. Cut down might mean no stored procedures for example.

The issues will be

upgrading from existing access systems since the sql code, although similar is not exactly the same - use of wildcards, being able to use vba functions in queries, etc

keeping db back and front end development simple for 90% (my guess) of developers
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 06-22-2017, 01:29 PM   #14
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Why indexing is important for good performance

not sure if anyone has ever played with linked lists, binary trees, and so on. Difficult in VB/VBA as you really need a pointer data type.

If you have you realise what a useful (and elegant) structure it is.

What databases use is a different form of indexing called a b-tree, which achieves efficient indexing of a large data set, with relative few levels - therefore allowing data to be retrieved very quickly.

https://en.wikipedia.org/wiki/B-tree

I am pretty sure RDBS data managers will have highly optimised machine code to manage these structures, and the system overhead for maintaining additional indexes will be relatively trivial compared with the benefits. I expect.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-22-2017, 01:31 PM   #15
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,931
Thanks: 114
Thanked 2,990 Times in 2,719 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 isladogs is a splendid one to behold
Re: Why indexing is important for good performance

Hi Dave

Would you care to provide a simple example of this in practice?

__________________
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
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
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 01:46 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