Solved

Clear Jet Query Buffer

Posted on 2009-05-13
10
879 Views
Last Modified: 2013-11-27
Is there a way to clear out the Jet query buffer?  I want to experiment with a bunch of different queries that do the same thing to see which is most efficient.

Unfortunately, after I execute the first query, the other queries tend to run faster just because the results have been buffered by Jet.  So I can't really compare one to the other.

Any ideas?
0
Comment
Question by:dastrw
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 24376969
0
 
LVL 2

Author Comment

by:dastrw
ID: 24377092
We have a rather complicated system which may be limited by the Jet engine and the standard crappy ODBC driver we are stuck with.

I believe the only way to ultimately decide which query is the fastest is to run each from the same starting point.

I suppose I could keep bringing down and bringing up Access, but I was hoping for something a bit less clunky.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 24377137
You can achieve this by issueing:
dbEngine.Idle dbRefreshCache
  and then waiting the PageTimeout setting (default is 5 seconds).  After that time, any page in cache not read locked will be considered invalid.
  The other issue your going to have is controlling if a query is costed or not.  Normally after a compact or a design change, the query is not costed until it's first executed.  Once that's done, the costing plan is saved with the query and not repeated.  So you need to make sure that each and every time you test, you open the query and then save it.
  JET SHOWPLAN will show you how the query is costed and being executed.  It will however not work with sub-queries.
JimD.
0
 
LVL 57
ID: 24377162
<<and then waiting the PageTimeout setting (default is 5 seconds).  After that time, any page in cache not read locked will be considered invalid.>>
  I should have made this clearer.  The purpose of issuing the .Idle is to flush all pending writes to disk and release all read locks on the cache.  After that, you just need to wait for the pagetimeout setting.
JimD.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 24377174
This is the RefreshCache Method ... from Help:

RefreshCache Method
     

Forces any pending writes to .mdb files, and refreshes memory with the most current data from the .mdb file.

Syntax

JetEngine.RefreshCache(Connection)

Parameters

Connection   The ADO Connection object for which to refresh the cache. An error will occur if the connection is not a valid, open ADO Connection. An error will occur if the provider used to create the connection does not support the RefreshCache method.

Remarks

You don't need to use this method in single-user environments unless multiple connections are made to the database. The RefreshCache method may increase performance in a multiuser environment because it forces the database engine to write data to disk, releasing locks on memory.

**** Example from Help:

RefreshCache Method Example
This example demonstrates the RefreshCache method.

Public Sub RefCache()

   Dim lateje As JRO.JetEngine
   Dim conn As New ADODB.Connection
   Dim conn2 As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim fld As ADODB.Field

   ' Open both connections to the database.
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data source=C:\Program Files\Microsoft Office\" & _
      "Office\Samples\Northwind.mdb;"
   conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data source=C:\Program Files\Microsoft Office\" & _
      "Office\Samples\Northwind.mdb;"

   Set lateje = CreateObject("JRO.JetEngine")

On Error Resume Next
   conn.Execute "drop table tab1"
On Error GoTo 0

   ' Create table and input new values for columns.
   conn.Execute "create table tab1 ( col1 int)"
   conn.Execute "insert into tab1 values (1)"
   conn.Execute "insert into tab1 values (2)"

   ' Flush the data so that conn2 can see the changes from conn1.
   lateje.RefreshCache conn2
   Set rs = conn2.Execute("select * from tab1")
   Set fld = rs.Fields(0)
   Debug.Print "Record 1: " & fld.Name & " = " & Str(fld.Value)

   conn.Close
   conn2.Close
   Set lateje = Nothing

End Sub

***

mx

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75
ID: 24377180
lol ... well, I was hoping JD would drop by ...

mx
0
 
LVL 2

Author Comment

by:dastrw
ID: 24377401
Thanks very much for all of the quick responses.  I'm going to have to test this out to see if it works.  I'll get back to you all real soon!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24378551
Naw, it won't work! ;-)
0
 
LVL 2

Author Closing Comment

by:dastrw
ID: 31581075
Thanks much for the help on clearing the cache ... and the good source of info on showplan.  The DBAs have given us minimal access to the DB and are reluctant to modify indexes, etc.  Being able to try lots of different queries is helping improve our app's performance despite these limitations.
0
 
LVL 75
ID: 24389789
"Grade:  A - "Thanks much for the help on clearing the cache ... and the good source of info on showplan.  The DBAs have given us minimal access to the DB and are reluctant to modify indexes, etc.  Being able to try lots of different queries is helping improve our app's performance despite these limitations."  "

You are welcome.

mx
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now