locked
Why does Jet Showplan show the same search multiple times? RRS feed

  • 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"
    

    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.html

    Thursday, 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