UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Slow Response Because Of Indexes, Access 2016    
 
   
bwilliams9901
post Aug 16 2019, 03:00 PM
Post#1



Posts: 5
Joined: 16-August 19



I was always under the impression that indexes would improve the overall performace of Access. Well in my case they appear to decrease performance.

Details: It's a local Access 2016 accdb database, I inherited. For my test, I started with a blank database and imported a single table from production and ran Compact & Repair.
DB_A = import of the production table with all original indexes (19 indexes)
DB_B = import of the production table with all the indexes removed except the Primary Key index (auto-number field)
Both databases have the single table and a single query.
Table (tblDetails): 435,593 records, 104 fields (mostly short text), Primary Key = Auto Number field, 19 indexes (including PK) in the original file
Group By/Unique query of each index field (excluding the PK) results in 1754 or less unique values. Cycle field has 11 results.

I run a simple query:
SELECT Cycle, Ext, ReimTxt FROM tblDetails WHERE Cycle="11" AND Ext=True; (Cycle does have an index in DB_A)
After the query pops up the data on the screen, I click the Last Record icon/button at the bottom of the query window.
Here's the strange part: DB_A (w/indexes) takes 50 seconds to reach the last record. DB_B (1 index) takes 2 seconds to reach the last record.

a) Does anyone have any idea why the additional indexes impact, negatively, pulling the data and updating the display?
b) I remember reading somewhere that it may not be benefical to create indexes that don't change much over the full range of the data. Ok that makes sense, but in my simple query I'm only using one field with an index. If the other fields and indexes are not used in the query, how are they impacting the query performance?

Thanks for any help,
BWilliams
Go to the top of the page
 
theDBguy
post Aug 16 2019, 03:44 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,288
Joined: 19-June 07
From: SunnySandyEggo


Hi BWilliams. Welcome to UA! welcome2UA.gif

You might give this article a try and let us know what you find out. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Aug 16 2019, 03:58 PM
Post#3


UtterAccess VIP
Posts: 1,602
Joined: 4-June 18
From: Somerset, UK


104 fields and 19 indexes are both very large for a table.
You haven't said whether the query fields are all indexed nor whether the time to load the query for indexed/non-indexed is noticeably different.
Indexing should significantly speed up searching but it does slow down update queries … as the indexes also need to be updated.

Once the query has loaded I'm not sure whether indexes still have any effect.
I'd be interested in the results you get from viewing each of the query execution plans with my JET ShowPlan Manager app.

--------------------
Go to the top of the page
 
dale.fye
post Aug 16 2019, 04:07 PM
Post#4



Posts: 160
Joined: 28-March 18
From: Virginia


I've always found that choosing what fields to index is more art than science.

I did recently read an article by Brent Ozar (SQL Server fame) and he recommends no more than 3-5 indexes for a table.

Indexes slow data entry. Records have to be inserted into internal "tables" associated with each index, so the more indexes, the longer this takes
Indexes tend to improve data queries where the indexes are on the fields being created, so I would start out by removing indexes on fields you do not use in queries.
Then, if you use the same two or three fields in almost every where clause against a table or query, I would create a composite index on those fields.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
isladogs
post Aug 16 2019, 04:38 PM
Post#5


UtterAccess VIP
Posts: 1,602
Joined: 4-June 18
From: Somerset, UK


Suggest you also try the same go to last record test on the actual tables themselves in both databases.

--------------------
Go to the top of the page
 
PhilS
post Aug 17 2019, 10:59 AM
Post#6



Posts: 614
Joined: 26-May 15
From: The middle of Germany


QUOTE
SELECT Cycle, Ext, ReimTxt FROM tblDetails WHERE Cycle="11" AND Ext=True; (Cycle does have an index in DB_A)
After the query pops up the data on the screen, I click the Last Record icon/button at the bottom of the query window.
Here's the strange part: DB_A (w/indexes) takes 50 seconds to reach the last record. DB_B (1 index) takes 2 seconds to reach the last record.

How many records are in the result?
Retrieving records via an index is a two step operation. 1.) Retrieve the matching index entries, 2.) retrieve the associated record to each index entry.
Without the index, the DB engine will do a table scan and retrieve the record right away.
If you are retrieving a small result set, the index will be faster. If you retrieve a very large result set, the table scan will be faster.

I think, I also explained that in my video on indexing tables. - At least I should have.
This post has been edited by PhilS: Aug 17 2019, 11:00 AM

--------------------
Go to the top of the page
 
jleach
post Aug 17 2019, 05:19 PM
Post#7


UtterAccess Editor
Posts: 10,074
Joined: 7-December 09
From: St Augustine, FL


I'll second Dale's sentiment here: finding the correct balance of indexes to use is a bit of an art, and rarely exceeds more than a small handful in each table.

If you really want to dig into it, I highly recommend this book/site:

https://www.amazon.com/Performance-Explaine...t/dp/3950307826
https://use-the-index-luke.com/SQL/table-of-contents (this site has all the content online, though I prefer the printed copy myself)

--------------------
Go to the top of the page
 
bwilliams9901
post Aug 19 2019, 10:20 AM
Post#8



Posts: 5
Joined: 16-August 19



Isladogs/Colin – Of the 3 fields selected, only the Cycle field has an index.

I agree, once the query has loaded, the indexes should have no effect. Also the fact that the SELECT statement doesn’t have an ORDER BY, should mean the indexes are possibly only used by the WHERE part of the statement.
One of the things that I don’t understand with Access, is after the Select statement pulls all the requested data from the table into memory, why is the quantity of indexes affecting the displayed results? It doesn’t make any sense.
Sorry, because of company PC restrictions, I can’t download your JET ShowPlan Manager app.

Dale.fye/Dale – I agree selecting indexes is more art than science, or perfected by benchmark testing. I agree with several of the responses, the table probably has too many indexes. But as I said, I inherited the database and haven’t had time to really look into the structure of the many tables, and excess indexes should not have a large impact on a Select statement (Inserts yes and maybe Updates).

I will look for the article from Brent Ozar.
Agreed, indexes will impact the inserts. But the issue I’m seeing is with a Select statement (with no Order By) which is not impacted by updating the indexes.
The original database creator probably created all the indexes because they are used for various table linkages and/or Where clauses.

Isladogs/Colin – (Suggest you also try the same go to last record test on the actual tables themselves in both databases.) Very good suggestion, I tested it this morning and when I open the table and then jumped to the last record, it’s almost instantaneous. Back to my original question, what is Access doing under the hood with a simple Select statement?

PhilS – the results contains 2832 records (oops I forgot that detail in my original question). I don’t have any idea if Access is using the index or table scan. SQL Server provides this type of information, but Access hides the “under the hood” details.
I will watch your video later today.

Jleach/Jack – I will check out your recommended books later today.

----------

Back to my original issue, I can’t find any explanation why the quantity of indexes would impact the performance of a simple 3 field Select query on a local database, particularly when the results are small enough (2832 records) they should fit into RAM. Strange!

Thanks for everyone's help and suggestions,
BWilliams
Go to the top of the page
 
theDBguy
post Aug 19 2019, 10:41 AM
Post#9


Access Wiki and Forums Moderator
Posts: 76,288
Joined: 19-June 07
From: SunnySandyEggo


Hi. Quick question, with the slow query, what happens if you go back to the first record after going to the last record the first time and then go back to the last record again? Does it still take about 50 seconds the second time around?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bwilliams9901
post Aug 19 2019, 12:10 PM
Post#10



Posts: 5
Joined: 16-August 19



theDBguy - Basically instantaneous. Which tells me the query pulled all the data into memory.

Thanks,
BWilliams
Go to the top of the page
 
theDBguy
post Aug 19 2019, 12:19 PM
Post#11


Access Wiki and Forums Moderator
Posts: 76,288
Joined: 19-June 07
From: SunnySandyEggo


Okay, that makes sense. So, here's my interpretation, which could be wrong, so please take it with a grain of salt. This is just one person's humble opinion.

So, I can't find a reference right now, but I have heard when Access pulls the data for a query, it only retrieves enough rows/records to display the first page. As far as I know, it doesn't pull all the data at once and put them into memory. It only retrieves the next set/page of data when the user needs or asks for them. So, when you run your query, it displays the first page of the data. And when you click on the "last record" navigation button, then it forces Access to retrieve all the data (otherwise, it would have only pulled the next page, one page at a time, and so on). As it does this, it naturally (I think) has to use the indexes as it retrieves the rest of the data. Therefore, the more index it has to evaluate, the slower the retrieval process would be. But once all the data are loaded into memory, then Access doesn't have to do the same process anymore.

Hope it makes sense (and close enough to the truth). Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bwilliams9901
post Aug 19 2019, 02:20 PM
Post#12



Posts: 5
Joined: 16-August 19



theDBguy - Ok that makes sense to me, except why would the extra indexes, which I assume are not used because they are not part of the Select statement, impact the responsiveness of the full dataset?

Go to the top of the page
 
theDBguy
post Aug 19 2019, 02:25 PM
Post#13


Access Wiki and Forums Moderator
Posts: 76,288
Joined: 19-June 07
From: SunnySandyEggo


Hi. I can't really answer that question because I am not schooled in the inner workings of indexes. You are assuming the indexes are not being used because the indexed fields are not part of the SELECT statement. I can't say if that assumption is true or not. Hopefully, someone else could provide some clarification about that area. At this point, I can't exclude the possibility that the additional indexes are being used based on the behavior you are describing. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Aug 19 2019, 02:43 PM
Post#14


UtterAccess VIP
Posts: 1,602
Joined: 4-June 18
From: Somerset, UK


The query execution plan should give you the answer to your questions about indexes.
Although you say you aren't able to download my JET ShowPan Manager, you can still set this up for yourself by following the instructions in my article.

--------------------
Go to the top of the page
 
bwilliams9901
post Aug 22 2019, 08:55 AM
Post#15



Posts: 5
Joined: 16-August 19



isladogs - My office has locked that section of the registry. I will need to ask someone in IT to add the JETSHOWPLAN setting.

I'm running Win10 64bit, with Access 2016 32bit. Based on your documentation, I need to add the JETSHOWPLAN=On setting to the HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Debug\ key.

Thanks,
BWilliams
Go to the top of the page
 
isladogs
post Aug 22 2019, 08:59 AM
Post#16


UtterAccess VIP
Posts: 1,602
Joined: 4-June 18
From: Somerset, UK


Well you could add the registry keys using VBA as I do in my utility. The article explains what is required.
Whether you want to go ahead and risk upsetting your IT colleagues is a decision only you can make

EDIT
Are you aware that you can only edit the HKLM hive from Access if you have opened it using Run As Administrator option. It may not actually be restricted by IT after all!

--------------------
Go to the top of the page
 
PhilS
post Aug 23 2019, 03:19 AM
Post#17



Posts: 614
Joined: 26-May 15
From: The middle of Germany


QUOTE
Are you aware that you can only edit the HKLM hive from Access if you have opened it using Run As Administrator option. It may not actually be restricted by IT after all!

In most corporate IT environments, you will be prompted to enter the credentials of a real Administrator account when you attempt to "Run As Administrator" , because your account isn't.

--------------------
Go to the top of the page
 
jleach
post Aug 23 2019, 07:36 AM
Post#18


UtterAccess Editor
Posts: 10,074
Joined: 7-December 09
From: St Augustine, FL


I'm not sure if this one is applicable or not, but in many cases you can use the same key in HKCU instead of HKLM, which does not require elevated rights.

--------------------
Go to the top of the page
 
isladogs
post Aug 23 2019, 07:55 AM
Post#19


UtterAccess VIP
Posts: 1,602
Joined: 4-June 18
From: Somerset, UK


I don’t believe HKCU will work in this particular case but by all means try it

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 03:20 AM