Results 1 to 11 of 11
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Opening Reports with a Where condition (All Versio

    When I want to open a report showing just some data, I can either:

    * Build the report on a query that takes parameters from a form, or
    * Use a very general query, and use the form to build a where clause.

    I used to always do it the first way, now I am very attracted to the second, largely because it gives me much more flexibility. I can for instance open the report from a number of different forms without that creating problems.

    My questions is : is there any difference in efficiency or speed between the two methods ?

    Most of the time I don't notice any performance decrease using a where clause, but I had a situation recently where the data was coming from an sql-server back end, with large amounts of data. A where clause was very slow, but a parameter query was OK. It seemed to me that sql-server handled the parameter query, but left the where clause to Access.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Reports with a Where condition (All Versio

    SQL Server may handle the parameter query nicely, but it won't handle a parameter query based on a form at all, since SQL Server knows absolutely nothing about Access forms.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Opening Reports with a Where condition (All Ve

    I have had very little to do with SQL_Server back ends.

    When I build a query in Access (using a form for parameters) it works. I imagined that it was working more efficiently than if the back-end was in Access.
    Does the query still execute on the local machine after retrieving all the data, or does the query get executed at the server and just return the relevant records?
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Opening Reports with a Where condition (All Ve

    It depends on a number of things. If you are connecting to SQL Server using ODBC, then if you assemble the SQL yourself, the ODBC driver should pass a definitive SQL string to the ODBC driver and not try to do the work in Jet. On the other hand, where you have a Form reference in a saved query, in at least some situations it seems that Access gets the entire recordset and then applies the WHERE clause. In those kind of cases, if performance is a major issue (looking at 50K or 100K records), we create a pass-through query, save it and then execute it so that we are sure SQL Server is doing the real work. It can make orders of magnitude of difference in response time.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Reports with a Where condition (All Ve

    Jet queries are always processed by the front end. You're going to have to follow Wendell's recommendations on pass through queries or stored procedures if you want to really improve the performance by transferring the processing to the back end.
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Opening Reports with a Where condition (All Ve

    Thanks

    My main concern is actually nothing to do with SQL_Server, I was just mentioning that as an example.

    My main concern is with Access back ends, where I work nearly all the time.

    Is there any real difference there between building criteria into a query and adding them in as a where condition when you open the report?
    Regards
    John



  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Opening Reports with a Where condition (All Ve

    I think that's going to depend on the size of the underlying recordset and the design of the query. Certainly a query that doesn't use references to a specific form or report is going to give you a more flexible reporting system. I personally have never noticed a performance hit passing a WhereCondition to a form or report. To me, that seems the fastest, as well as the cleanest, method to use. I think you're going to have to do some testing in your particular circumstances and decide for yourself. Just be aware that there is a huge difference between passing a *filter* in the DoCmd.Open statement and passing a Where condition. Filters have been much slower, in my experiences, since the entire recordset is loaded then fitlered, while the WhereCondition seems to be quite fast.
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Opening Reports with a Where condition (All Ve

    Thanks

    I have found that passing a Where condition does not seem to cause any real performance loss, but I haven't done any systematic testing.

    I will try a few experiments.
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Opening Reports with a Where condition (All Ve

    I attach a little db that tries to measure this in one case.

    Similar reports are opened from a form using a "where" clause, a parameter query, a query with criteria hard coded, a filter clause and no condtions at all.

    In each case the time of the button click is captured using an API function adh_apiGetTime().

    Next the time that the report header format event occurs is also captured and the difference calculated.

    There is quite a bit of variation within the same method, and some variation between methods, but not that much.

    The data comes from two tables. Initially I had 17,000 recs in one table, but I have had to cut that back to post it. Cutting back didn't speed it up much.
    Attached Files Attached Files
    Regards
    John



  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening Reports with a Where condition (All Ve

    John,

    The timing functions have a "granularity" of about 15.5 ms. And because of caching and/or optimizing, the first time you run a report often takes slightly longer than subsequent times.

    After running all options once, all options took 47 ms. I then I extended the table to over 200,000 records, compacted, and tested. Again, all options took 47 ms. So my conclusion would be that for all practical purposes, there is no difference...

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Opening Reports with a Where condition (All Ve

    Thanks Hans

    Your results are consistent with mine. I am not sure that they 'prove' anything, but they make me feel more comfortable about using 'where ' clauses.

    I split the db with the data accessed over a network connection. Two things changed:
    * the reports took a bit longer to open, but not as much I would have expected (I was also using a slower machine)
    * the parameter query method was consistently considerably slower than all the others.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •