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
> Optimizing A Query, Access 2016    
 
   
pere_de_chipstic...
post Jul 10 2019, 10:29 AM
Post#1


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi All

I have the following query:
SQL
SELECT TblCalibItems.CalibItemID AS [Job No], tblEquipt.Equipment AS Instrument, tblCoEquipt.SerialNo AS [Serial No], tblCompanies.CompanyName AS Customer, [ConsignNo] & ' / ' & [SubConsignNo] AS Consignment, TblCalibItems.DateOfCal AS Calibrated, tblStatus.StatusAbb AS Result, tblConsign.DateDue AS Due
FROM tblStatus RIGHT JOIN (tblEquipt INNER JOIN (tblConsign INNER JOIN (tblCompanies INNER JOIN ((tblCoEquipt LEFT JOIN tblProcAuth ON tblCoEquipt.ProcID = tblProcAuth.ProcID)
INNER JOIN (tblSubConsign INNER JOIN TblCalibItems ON tblSubConsign.SubConsignID = TblCalibItems.SubConsignID) ON tblCoEquipt.CoEquiptID = TblCalibItems.CoEquiptID) ON tblCompanies.CompanyID = tblCoEquipt.CustomerID) ON tblConsign.ConsignID = tblSubConsign.ConsignID) ON tblEquipt.EquiptID = tblCoEquipt.EquiptID) ON tblStatus.StatusID = TblCalibItems.Result
WHERE (((TblCalibItems.Status)<5) AND ((tblProcAuth.EmpID)=[TempVars]![intCurUser] Or (tblProcAuth.EmpID) Is Null))
ORDER BY TblCalibItems.CalibItemID;


The first time the query is opened (after a PC power up) it takes some 12 seconds to return 1180 records (from a table with over 190 thousand records), on subsequently opening the query, it takes less than 1 second to return the same records. I've checked the indexing on the various tables, removed unnecessary formatting and sorting, and removed subqueries but have been unable to reduce the processing time further. The database is split and the query is saved as a query object.

My client doesn't like this delay, which is worse on his 25 user system over a network. Is there anything about this query, or that I can check, that is causing it to run so slowly on first opening but subsequently allows it to run more than 10 times faster?

TIA

--------------------
Warm regards
Bernie
Go to the top of the page
 
DanielPineault
post Jul 10 2019, 12:16 PM
Post#2


UtterAccess VIP
Posts: 6,709
Joined: 30-June 11



Do you establish a persistent connection at the startup of your database?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
pere_de_chipstic...
post Jul 10 2019, 12:44 PM
Post#3


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Daniel

Yes, I open a persistent recordset (rstBE - for a one record table in the BE) that is opened on start up and remains open while the db is open.

--------------------
Warm regards
Bernie
Go to the top of the page
 
isladogs
post Jul 10 2019, 12:53 PM
Post#4


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


The only thing I can see that may possibly explain it is the use of Tempvars.
Have you tried omitting it from the query temporarily?

I suspect you've already covered all relevant points but you could have a look at this article on my website Optimise Queries. There may just be something there that will help

--------------------
Go to the top of the page
 
pere_de_chipstic...
post Jul 10 2019, 02:08 PM
Post#5


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Colin

I tried removing the temp var as you suggested but it made no difference frown.gif .

I followed your web site suggestions, from an earlier thread and this did help considerably - the original processing time was in the order of 50 seconds! What intrigues me is that once the query has been run once, the processing time reduces to about 1 second - even if the db is closed and re-opened. The processing time only goes back to 12 seconds if I close down and re-open the PC and start the db again iconfused.gif .

--------------------
Warm regards
Bernie
Go to the top of the page
 
dale.fye
post Jul 10 2019, 02:19 PM
Post#6



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


What are you doing with this query? Is it the recordsource for a form, or for a recordset?

Do you build the SQL string at runtime or is this a saved query. It may be that the query has to be optimized the first time it is run, and after that it uses the optimized query plan from the first run.

As isladogs indicates, it could have something to do with the tempvars. If you are using this query to populate a recordset, you might want to change it and remove that Tempvars reference and define a parameter query with the correct parameter declaration and give that a try. If that improves the situation, you could modify your code to something like:
CODE
dim db as dao.database
dim rs as dao.recordset
dim qdf as dao.querydef

set db = currentdb
set qdf = db.querydefs("yourQueryName")
qdf.parameters("EmpID") = clng(nz(Tempvars!intCurUser, 0))
set rs = qdf.openrecordset



--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
pere_de_chipstic...
post Jul 10 2019, 02:29 PM
Post#7


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Dale

Many thanks for your input.

It is a saved query and is used to populate a list box on a pop up form - though the tests and timings I've posted here are for opening the query directly from the Access window.

The query returns a list of outstanding tasks limited to those where the logged on user (defined by the tempvar) is authorized to use the associated procedure. The form allows the user to select the next task he/she wishes to complete or view.

--------------------
Warm regards
Bernie
Go to the top of the page
 
isladogs
post Jul 10 2019, 03:51 PM
Post#8


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


As its a saved query, the query execution plan will be saved until you next compact the database.
Just out of interest do you compact on close?

You could look at the query execution plan log created when the query is run for the first time and again subsequently.
My Jet ShowPlan Manager app should be useful for that. See http://www.mendipdatasystems.co.UK/jet-sho...ager/4594494791 or https://www.UtterAccess.com/forum/index.php...owtopic=2051922

--------------------
Go to the top of the page
 
pere_de_chipstic...
post Jul 10 2019, 04:10 PM
Post#9


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Colin

No the db does not compact on close - the db looks for the db size on start up and sets the compact on close flag if it increases by more than a set size.

I'll look at the execution log, as you suggest, and get back to you.

Cheers

--------------------
Warm regards
Bernie
Go to the top of the page
 
isladogs
post Jul 10 2019, 05:14 PM
Post#10


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


Are you sure its not just been compacted due to your rules when the time is slow?

--------------------
Go to the top of the page
 
pere_de_chipstic...
post Jul 10 2019, 05:27 PM
Post#11


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


>>Are you sure its not just been compacted due to your rules when the time is slow?<<
No, it does not C&R on closing as even switching the automated C&R off, the same thing occurs.

It is very consistent,
1. Having opened the query once and making no other changes, then C&Ring the db 'manually'
the query runs in less than a second

2. Having opened the query once; Closing the db and re-opening it
the query runs in less than a second

3. Closing the db, shutting down the PC, and then restarting;
on reopening the db the query takes 12 seconds to run

shrug.gif

--------------------
Warm regards
Bernie
Go to the top of the page
 
pere_de_chipstic...
post Jul 13 2019, 08:27 AM
Post#12


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Colin

Tried to run your Jet ShowPlan Manager app, but unfortunately it didn't want to work.

I opened the programme with Access run as Administrator
It reported A2019 under 365 was installed, when actually O2016 professional is installed, and the "Create SHOWJETPLAN key" button was disabled.

Am I missing something?

--------------------
Warm regards
Bernie
Go to the top of the page
 
isladogs
post Jul 13 2019, 09:12 AM
Post#13


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


OK several things here.
MS made it VERY difficult to distinguish between both A2016 & A2019 as both are version 16.0

As the retail version changeover occurred at around 16.0.10827.20138, I've used that as the cut-off point
Anything above that version number is counted as A2019.

Similarly there is no obvious way of determining Access 365.as the version numbering is basically identical
The app looks for the presence of a ClickToRun registry key similar to this:
CODE
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines"

or
CODE
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines"


If found, you have (or had), Office365 installed on your computer.
Unfortunately if you tried Office 365 at any time and then removed it, the ClickToRun key isn't always removed

If you can come up with a better way of distinguishing A365/2019/2016 I'd be very pleased to hear it! grinhalo.gif

The Create JETSHOWPLAN key is disabled by default.
It should be ENABLED if you have run the app as an Administrator.
Can you check whether you really have done so

In the Immediate window, type: ?IsUserAnAdmin
a) If the response is 1, you are running Access as an Administrator
b) If the response is 0, Access is NOT being run as an Administrator

In each case, a label on the main form will say in bold red text:
a) The application is being run as an Administrator
b) To edit the SHOWPLAN value, the application MUST be run as an Administrator

Hope that helps

--------------------
Go to the top of the page
 
pere_de_chipstic...
post Jul 13 2019, 10:33 AM
Post#14


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Colin

Thank you for that, still no luck I am afraid.

The registry location I have is:
CODE
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines"
and under that are a number of other locations - ACE, Excel, Exchange, OData, ODBC, Sharepoint, Text, Xbase

?IsUserAnAdmin in the immediate window returns 1
"The application is being run as an Administrator" appears in red on the form
The "Create JETSHOWPLAN key" is disabled iconfused.gif


Attached File(s)
Attached File  JetShowPlan.JPG ( 76.42K )Number of downloads: 1
 

--------------------
Warm regards
Bernie
Go to the top of the page
 
pere_de_chipstic...
post Jul 13 2019, 11:17 AM
Post#15


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Hi Colin

Good news is that by holding the shift key down when opening JETSHOWPLAN, and then opening the form did enable the "Create JETSHOWPLAN key" and seemed to work correctly

Not so good news is that on running the query in my db there was no entry added to the showplan.out file!

--------------------
Warm regards
Bernie
Go to the top of the page
 
isladogs
post Jul 13 2019, 12:27 PM
Post#16


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


Hmmm?
It has been extensively tested by several users with many different versions and bitnesses including your setup.
The Form_Load code includes this code:

CODE
'check if run as administrator
    If IsUserAnAdmin = 0 Then
        Me.cmdShowPlan.Enabled = False
        Me.cmdView.Visible = False
        Me.lbladmin.Caption = "To edit the SHOWPLAN value, the application MUST be run as an Administrator"
    Else
        Me.lbladmin.Caption = "The application is being run as an Administrator"
        Me.cmdShowPlan.Enabled = True
        CheckShowPlanValue
    End If


So I'm unsure how your situation has occurred

Are you saying a SHOWPLAN.OUT file is created but remains blank? Or no file is created?
Just possibly there may be another file created elsewhere

That registry key is for Access 365. If you aren't using 365 you probably don't need that key
Do you have another similar key for the retail version
CODE
HKEY_LOCAL_MACHINE\ SOFTWARE\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\

If so, try adding the Debug\JETSHOWPLAN entries in that section

Good luck

P.S. If you want to continue discussion about this the issues you are having with this utility, I suggest we do so via PM or email.

--------------------
Go to the top of the page
 
AlbertKallal
post Jul 13 2019, 10:55 PM
Post#17


UtterAccess VIP
Posts: 2,819
Joined: 12-April 07
From: Edmonton, Alberta Canada


Try this trick:

CODE
Dim strSQLFirst    as string
Dim strWhere       as string


strSQLFirst = "your SQL right up to the WHERE clause"

strWhere = " WHERE (tblCalibItems.Status < 5) AND " _
            (tblpocAuth.EmpID = " & [TempVars]![intCurUer] OR " & _
                      (tblProcAuth.Emp is null) " & _
                  " ORDER BY TblCalibItems.CalibItemID.



At this point, it not clear if you launching a report, or a form, but say a form?

Well, then above can be in the forms on-load (and you remove the forms record source.

Me.RecordSource = strSQLFirst & strWhere

IN fact, try the form based on the query without the where clause, and pass the above “where clause” to the form, or report, and see how well it works.

By “evaluation” of the parameters, then JET can make far better guesses as opposed to it being supplied with parameters.

Regards
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
dale.fye
post Jul 14 2019, 07:46 AM
Post#18



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


@pere_de_chipstick

Ok, you mention this delay happens the first time the query is run, and all of your tests are being done from the query designer.

But you also indicate that you have a persistent connection to the BE, which generally doesn't occur unless your application is running.

I would then create a Public subroutine to test the time it takes to run the query.

CODE
Public Sub TestQuery(queryname as string)

    dim db as dao.database
    Dim rsP as dao.recordset   'used to create a persistent connection to the BE
    dim rs as dao.recordset

    dim StartTime as date

    set db = currentdb

    set rsP = db.Openrecordset("Select top 1 from SomeTable")

    StartTime = Now()
    set rs = currentdb.querydefs(Queryname).openrecordset
    rs.movelast

    debug.print "Elapsed time: " & DateDiff("s", StartTime, Now())

    rs.close
    set rs = nothing
    rsP.close
    set rsP = nothing

End Sub
Run that a couple of times and see what results you get.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 05:13 AM