Solved

Clear Jet Query Buffer

Posted on 2009-05-13
10
875 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

760 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

21 Experts available now in Live!

Get 1:1 Help Now