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
> Force Update Of Query Statistics, Access 2016    
 
   
bwilliams9901
post Sep 16 2019, 10:47 AM
Post#1



Posts: 9
Joined: 16-August 19



Does anyone know how to force Access2016 to update query statistics?
Setup: Access 2016 32bit running on Win10 64bit, inherited database, everything in one ACCDB file (no FE/BE architecture yet), problem query is an Update query with 6 tables total, all have indexes.

The following summarizes my benchmark testing:
a) Original query = updates 7162 records, run time around 500seconds
b) Compact & Repair database = run time around 500seconds
Based on various web articles, Access is supposed to run the query recompile process after a Compact & Repair, doesn’t appear to be true. Maybe previous versions it worked
c) Copy/paste query to a new query = run time around 500seconds
Assumption – new query should have no previous statistics saved (apparently wrong assumption)
d) Edit (add field)/Save/Remove field/Save = run time around 500seconds
I think previous versions of Access would force a query recompile, apparently Access2016 = no. Or I'm remembering incorrectly.
e) Edit add Counter [AutoNumber field] <> 0/Save = run time around 500seconds
Again, editing a query should cause a query recompile. This change caused a change in the Where clause and should have required a recompile.

f) Create a new query by copying the original SQL statement and creating a new query = run time around 7seconds

Can anyone explain why a new query only takes 7 seconds vs all the other attempts take over 500seconds?? Several of the edits/changes should have caused the query to recompile and update statistics.

Thanks for any help, I'm puzzled
BWilliams
This post has been edited by bwilliams9901: Sep 16 2019, 10:59 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 16 2019, 11:03 AM
Post#2


UA Admin
Posts: 35,888
Joined: 20-June 02
From: Newcastle, WA


I didn't know that doing a Compact & Repair on an accdb would force queries in it to be recompiled. Can you provide the link(s) so I can learn more about that? Thanks.

It's interesting that the replacement query updates the same number of records in approximately 70 times faster. That's a big difference. Is it replicable many times? Or was that a one-time phenomenon?

Editing and saving an existing query should, in theory, cause it to be recompiled, so I'm surprised that it had so little impact on your query. Again, is that replicable multiple times?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bwilliams9901
post Sep 16 2019, 11:19 AM
Post#3



Posts: 9
Joined: 16-August 19



One thing I forgot to mention in my Setup, the database is local.

Yes running the copy/paste SQL run time is repeatable, around 7 seconds.

Apparently I misunderstood that query statements are recompiled after a Compact & Repair. It appears the TABLE statistics are updated. My bad.
http://www.databasedev.co.UK/compacting-an...-ms-access.html
The 4th paragraph = “The utility also updates table statistics to reflect database characteristics of the restructured data.”

https://www.fmsinc.com/microsoftaccess/comp...t-on-close.html
“Updates table and index statistics, so the query optimizer can choose the most efficient query plan.”

Go to the top of the page
 
GroverParkGeorge
post Sep 16 2019, 12:21 PM
Post#4


UA Admin
Posts: 35,888
Joined: 20-June 02
From: Newcastle, WA


Since you are running updates, it might be useful to compare the runtime with current indexes in place and again without them.

While indexes do improve retrieval times for some SELECT queries, updating indexes might actually be slower.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bwilliams9901
post Sep 16 2019, 12:34 PM
Post#5



Posts: 9
Joined: 16-August 19



I agree too many indexes can slow down Updates, but in this case the tables are identical, only the query statistics are different.

As I said, the thing that puzzles me the most, all the logical techniques to force the statistics to update made no change. I wish Access had a command like SQL Server, "Update Statistics". Then I could force the update.

I'm now wondering if I should rebuild ALL the queries in the database (over 1000).
Go to the top of the page
 
GroverParkGeorge
post Sep 16 2019, 01:56 PM
Post#6


UA Admin
Posts: 35,888
Joined: 20-June 02
From: Newcastle, WA


Are you aware of, and using JET ShowPlan?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bwilliams9901
post Sep 16 2019, 02:01 PM
Post#7



Posts: 9
Joined: 16-August 19



The company I work for has locked that section of the Registry. They also have prevented the ability to Run As Administrator, so I cannot run isladogs Jet ShowPlan Manager. Bummer!!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 07:46 AM