Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 910
  • Last Modified:

Clear Jet Query Buffer

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
dastrw
Asked:
dastrw
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
0
 
dastrwAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
lol ... well, I was hoping JD would drop by ...

mx
0
 
dastrwAuthor Commented:
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
 
GRayLCommented:
Naw, it won't work! ;-)
0
 
dastrwAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now