Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Clear Jet Query Buffer

Posted on 2009-05-13
10
Medium Priority
?
905 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

604 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