?
Solved

Clear Jet Query Buffer

Posted on 2009-05-13
10
Medium Priority
?
900 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 
LVL 58
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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 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
 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

771 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