Asked by:
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"
- Moved by Emi ZhangMicrosoft contingent staff Wednesday, February 28, 2018 6:11 AM Move
Tuesday, February 27, 2018 3:42 PM
All replies
-
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 tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.Wednesday, February 28, 2018 6:09 AM -
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 MSDNFSF@microsoft.com.Thursday, March 1, 2018 3:15 AM -
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.
- Edited by bobo bobby Thursday, March 1, 2018 10:09 PM
Thursday, March 1, 2018 9:37 PM -
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.htmlThursday, March 1, 2018 11:28 PM -
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"
Thursday, March 1, 2018 11:55 PM -
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"
- Edited by bobo bobby Friday, March 2, 2018 12:00 AM
Friday, March 2, 2018 12:00 AM -
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 MSDNFSF@microsoft.com.Monday, March 5, 2018 6:39 AM