Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002

    Angry Unanswered: "group by" slowing down queries

    Hi all. We have an Access database in which we are trying to run some subqueries to total and group some items. It seems the "group by" parameter is all but killing the query, making them take 10 minutes to run, when they run in about 3 seconds before adding the "group by". Here's the basics.

    Query 1:
    Gathers some basic information with a few where clauses. Runs in about 3 seconds.

    Query 2:
    If I run this query, and just have it display all of the fields from query 1, it also runs in about 3 seconds.

    If I run the same query, but first insert the "total" line in the bottom grid, it displays "group by" under each field. Try to run it now, and it takes 10 minutes!

    All I did was add that total line! Why is this killing my queries?

    Any thoughts or suggestions would be greatly appreciated!

  2. #2
    Join Date
    Aug 2003

    Table Design

    Whatever you are running the Total on, make sure that in the Table Design view, the column is marked as 'Indexed' - Yes (Duplicates OK) or Yes (No Duplicates)

    Try adding the Indexing to all the fields that you are specifying criteria for in the query.

    This means that changes to the structure of the table will take longer to save but searches, queries and SQL will run through the table in half the time.

    Let me know if this doesn't work

  3. #3
    Join Date
    Nov 2002
    I'll give that a try. However, right now, just trying to play with it, I don't even have a "total". All the fields are "group by". . . .

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland

    at least total/average/count/whatever one of the fields for your experiments or the query is just eating cpu ticks for nothing. it becomes a very expensive SELECT *

    as well as following the index recommendation earlier, worry whether you need ALL the fields in the query. it is slightly unusual to groupby everything

    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2002
    I agree that we without a "sum" or "average" or something that the query really isn't doing anything. What we want for production will have averages and sums.

    My point here though, is that the query runs fine until we add that "total" row to the query builder. There needs to be something in each field for the "total" and the default is "group by".

    We will eventually sum and average fields, but for now, I'm trying to make this as simple as possible to narrow down where the issue is. So far all I know is that adding any kind of "total" criteria kills my query.

    Thanks for you help.


  6. #6
    Join Date
    Oct 2003
    Boston, Mass. USA
    how many records in the underlying tables? how many fields in your query? What version of Access are you using?

    is the query in one database pulling from tables in a back end, another MDB?
    versions of Acces before 2002 pull all the records from the table across the server before filtering them. So if your table has 100,000 or a million records in it and/or if your server is often slow anyhow ....

    try putting the query in the same MDB as the tables, try to run the queries on the same machine the MDB is located. If that improves performance at all this might be the problem.

    If you have a SQL back end see if you can create a view (query) on the SQL side tn the server to filter some of your data instead of doing it all thu queries and sub-queries.

    some possible ideas .... good luck!


Posting Permissions

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