Why does Jet Showplan show the same search multiple times?

Question

MS Access 2010 with split FE/BE and the BE on a separate server.  The connection to the server is continuous because of a form that is always open.

When I execute any of my queries, Showplan indicates that they are actually run multiple times.  The report below was generated when I manually ran one query one time, but it seems that my single execution resulted in the query actually being  executed twice.  Is that an expected behavior?

---------------------------------------------
DATE: 0
VER:  15.00.4849

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
    Using index 'MRN'
    Having Indexes:
    MRN 22409 entries, 82 pages, 22409 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    id 22409 entries, 39 pages, 21752 values
      which has 1 column, fixed, clustered and/or counter
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Restrict rows of table procedure_list
      using rushmore
      for expression "((Procedure_list.studydate=forms!traffic_control!scheduledate) OR (Procedure_list.studydate Is Null)) OR ((Procedure_list.status<4) AND (Procedure_list.studydate<forms!traffic_control!scheduledate))"
02) Inner Join result of '01)' to table 'patient_data'
      merging indexes
      join expression "Procedure_list.MRN=patient_data.MRN"



--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Inner Join table 'patient_data' to table 'procedure_list'
      using for Sample Preview



--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
    Using index 'MRN'
    Having Indexes:
    MRN 22409 entries, 82 pages, 22409 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    id 22409 entries, 39 pages, 21752 values
      which has 1 column, fixed, clustered and/or counter
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Restrict rows of table procedure_list
      using rushmore
      for expression "((Procedure_list.studydate=forms!traffic_control!scheduledate) OR (Procedure_list.studydate Is Null)) OR ((Procedure_list.status<4) AND (Procedure_list.studydate<forms!traffic_control!scheduledate))"
02) Inner Join result of '01)' to table 'patient_data'
      merging indexes
      join expression "Procedure_list.MRN=patient_data.MRN"



--- temp query ---

- Inputs to Query -
Recordset
- End inputs to Query -

01) Scan recordset
02) Restrict rows of table 01)
      using index 'IdxFKPrimaryScalar'
      for expression "[_Data]=FK"



--- temp query ---

- Inputs to Query -
Recordset
- End inputs to Query -

01) Scan recordset
02) Restrict rows of table 01)
      using index 'IdxFKPrimaryScalar'
      for expression "[_Data]=FK"

Answer 1

Hi,

Based on your description, I will move your thread to Access for Developer forum:

https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi

---

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [e-mail].


Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

Answer 2

Hi bobo,

How did you configure Showplan? Is the BE access database or sql database?

>> When I execute any of my queries, Showplan indicates that they are actually run multiple times

How did you execute your queries?

Based on the result, it seems you query a join query, I suggest you try to run a simple select query.

Best Regards,

Tao Zhou

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Thanks for you answer. The back end database is an access file, not a SQL file.  

I executed the query by opening it in design view and then switching to form view.

This is a simple select query, though it uses data from two tables.  I'm not sure how else to do it, since I need both tables to populate the form after the query has been run.

Answer 4

What do you get from Showplan if you just open the query (or form) directly from the nav pane, rather than first opening it in design view, and then switching to datasheet view (or form view)?

My question is based on the theory that, when you open the query in design view, Access may do a preliminary run of the query just to see what elements have to be displayed in design view.  I  don't know that this is the case, but it might be the explanation.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

It looks the same to me.

I'm completely clearing the Showplan file by opening it, deleting all text, and then saving it.  I'm then closing it as an empty file.  When I subsequently run the query, either as I described above or directly from the nav pane, it seems to actually query the tables twice.

--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
    Using index 'MRN'
    Having Indexes:
    MRN 22438 entries, 77 pages, 22438 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    id 22438 entries, 39 pages, 21777 values
      which has 1 column, fixed, clustered and/or counter
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Restrict rows of table procedure_list
      using rushmore
      for expression "((Procedure_list.studydate=forms!traffic_control!scheduledate) OR (Procedure_list.studydate Is Null)) OR ((Procedure_list.status<4) AND (Procedure_list.studydate<forms!traffic_control!scheduledate))"
02) Inner Join result of '01)' to table 'patient_data'
      merging indexes
      join expression "Procedure_list.MRN=patient_data.MRN"



--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Inner Join table 'patient_data' to table 'procedure_list'
      using for Sample Preview



--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
    Using index 'MRN'
    Having Indexes:
    MRN 22438 entries, 77 pages, 22438 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    id 22438 entries, 39 pages, 21777 values
      which has 1 column, fixed, clustered and/or counter
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Restrict rows of table procedure_list
      using rushmore
      for expression "((Procedure_list.studydate=forms!traffic_control!scheduledate) OR (Procedure_list.studydate Is Null)) OR ((Procedure_list.status<4) AND (Procedure_list.studydate<forms!traffic_control!scheduledate))"
02) Inner Join result of '01)' to table 'patient_data'
      merging indexes
      join expression "Procedure_list.MRN=patient_data.MRN"



--- temp query ---

- Inputs to Query -
Recordset
- End inputs to Query -

01) Scan recordset
02) Restrict rows of table 01)
      using index 'IdxFKPrimaryScalar'
      for expression "[_Data]=FK"



--- temp query ---

- Inputs to Query -
Recordset
- End inputs to Query -

01) Scan recordset
02) Restrict rows of table 01)
      using index 'IdxFKPrimaryScalar'
      for expression "[_Data]=FK"

Answer 6

Maybe this is a clue.  When I replaced the variable forms!traffic_control!scheduledate with an actual date,  Showplan has the query running just once.  But that's not reality for this query, which has to get its data from a variable on a form.

--- qry_list_by_date ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Inner Join table 'patient_data' to table 'procedure_list'
      using for Sample Preview



--- temp query ---

- Inputs to Query -
Table 'patient_data'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
    Using index 'MRN'
    Having Indexes:
    MRN 22438 entries, 77 pages, 22438 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    id 22438 entries, 39 pages, 21777 values
      which has 1 column, fixed, clustered and/or counter
Table 'procedure_list'
    Database 'Y:\Apps1\A-Soft\Backend\vir_case_ log_be.accdb'
- End inputs to Query -

01) Restrict rows of table procedure_list
      using rushmore
      for expression "((Procedure_list.studydate=#3/2/2018#) OR (Procedure_list.studydate Is Null)) OR ((Procedure_list.status<4) AND (Procedure_list.studydate<#3/2/2018#))"
02) Inner Join result of '01)' to table 'patient_data'
      merging indexes
      join expression "Procedure_list.MRN=patient_data.MRN"

Answer 7

Hi bobo,

In my option, it is difficult to trouble shot this issue without reproducing.

Could you share us a simple database which could reproduce your issue?

Best Regards,

Tao Zhou

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].