Results 1 to 9 of 9
  1. #1
    Join Date
    May 2020
    Posts
    7

    Question Query running fine as select query, but taking forever when making a table

    I want to convert my query's results into a table because I can't sort them within a reasonable time as they are now, but whenever I use either of the options "Make Table", "Append" or "Update", the query won't finish even when I let it run overnight, whereas it finishes in under an hour otherwise. Why is it so hard to save results that can clearly already be calculated? How do I make a make table query run as efficient as a select query on my 8.6 million records? I've tried using another SELECT INTO query to convert my main query's results into a table instead, but to the same result.

    This concerns the following query:

    Code:
    SELECT q.GeoID,
    
    (SELECT Sum(sq.Food) AS SumOfFood
    FROM Datafile_worldmap AS sq
    WHERE sq.x Between q.x-1 and q.x+1
    and sq.y between q.y-1 and q.y+1
    )-[q].[Food] AS Ring1,
    
    (SELECT Sum(sq.Food) AS SumOfFood
    FROM Datafile_worldmap AS sq
    WHERE sq.x Between q.x-2 and q.x+2
    and sq.y between q.y-2 and q.y+2
    )-[q].[Food]-[Ring1] AS Ring2,
    
    (SELECT Sum(sq.Food) AS SumOfFood
    FROM Datafile_worldmap AS sq
    WHERE sq.x Between q.x-3 and q.x+3
    and sq.y between q.y-3 and q.y+3
    )-[q].[Food]-[Ring1]-[Ring2] AS Ring3
    
    
    FROM Datafile_worldmap AS q;


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I do believe it is because an Access select query will retrieve a set of records up to a certain size from a large set and present enough of them to fill a page or two, but if you scroll past that point, it has to get an additional block of records. You might think the query has completed in the time frame you've mentioned but try moving beyond that point and you will likely find it starts running again - not from scratch, but to retrieve another block of records. Scrolling beyond that point, or (I think) reversing the sort order of a field might make that obvious. Try that when your query "finishes" running and if you don't find that to be the case, then I don't know why there would be such a difference in performance between the two, except to say there is added complexity for Access to determine the field properties on top of everything else going on. I suspect you will find that even to fully and completely run that query will take much longer than you think it does.

    I will say though that to make a table out of something as complex as you have there - 3 sub queries complete with calculations and totals is the most convoluted query I've ever seen. That row by row processing over 8 million records will probably take days! You will probably have to do it in chunks (i.e. separate partial tables). I would only use a MT query 1 time - to build a table with the desired properties and then update/append to it thereafter, whereas I suspect you intend to do this many times.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The volume of records would seem to account for processing time.
    It appears that you are "boxing a geo coordinate" and processing data within the box.
    Depending on whether this is one time or repeated or just a piece of your project, you may consider some local extracts of tables to work with. That is, a subset of records dealing with the "boxes" to be processed stored locally. Then perform the actual processes on the "smaller set of tables".

    Just thought for consideration. You know your requirement and data better than readers. Good luck.

  4. #4
    hci_jon is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Montgomery, IL
    Posts
    9
    A couple of quick thoughts on this:

    How is the Datafile_worldmap being accessed? Is this a table on a SQL server, a local table in Access, or perhaps an Excel or Txt file you are connecting as an external table?

    If it is not a table on an SQL server or Access file, consider creating a table from the Datafile_worldmap data as a local Access table. You could then also add indexes to that table to speed up your subqueries if necessary and run another SELECT INTO to create your resulting final summary table.

    If it is already a table can you add indexes to it? If so, add indexes to the fields you are using in your WHERE clauses in the subqueries and this will likely speed up the process considerably. If not, consider again selecting the data into your own table so you can index it.

    Finally, if the database is sitting on a network file share, this can also slow down the process considerably. Can you copy the database to your local PC to do this processing and then copy it back up if that is the case?

  5. #5
    Join Date
    May 2020
    Posts
    7
    I created Datafile_worldmap locally and indexed every field (idk if that's too much). GeoID is the primary key. I only need to analyse this data once. I made a Make Table query with only GeoID and Ring1, and it's been running for something close to 8 hours at this point. I'm using an i7-3770k and it sits at 15% usage due to the single core limit of Access. I'm considering to calculate Ring2 and Ring3 in separate files so each can use its own processor core, but I wish I knew the estimated time.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't know what you did during development regarding testing your code; test data,doing some timings etc.
    You might want to use just Ring1 as you suggested, but further restrict your x-1, x +1,y-1, y+1. That is, reduce the amount of processing to see if there is something wrong with the set up and/or ensure the result is what you expect.

    It might help if you showed us the table design and told us a little about your requirement. All we have really seen is the SQL you posted.

    Just grasping a little in hopes of moving things forward.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if your x and y fields are indexed you could try using non standard joins. It would be something like this

    Code:
    SELECT GeoID, R1 AS Ring1, R2-R1 AS Ring2, R3-R2 AS Ring3
    FROM 
    (SELECT q0.GeoID,
    
    Sum(q1.Food) AS R1,
    Sum(q2.Food) AS R2,
    Sum(q3.Food) AS R3,
    
    FROM ((Datafile_worldmap AS q0 
                 INNER JOIN Datafile_worldmap AS q1 ON q1.x>=g0.x-1 and q1.x<=g0.x+1 and q1.y>=g0.y-1 and q1.y>=g0.y+1) 
                 INNER JOIN Datafile_worldmap AS q2 ON q2.x>=g0.x-2 and q2.x<=g0.x+2 and q2.y>=g0.y-2 and q2.y>=g0.y+2) 
                 INNER JOIN Datafile_worldmap AS q3 ON q3.x>=g0.x-3 and q3.x<=g0.x+3 and q3.y>=g0.y-3 and q3.y>=g0.y+3 
    GROUP BY q0.GeoID)
    You do have a lot of data, so will still take some time, but should be faster than subqueries. By way of illustration, a client had a complex subquery which was taking around 18 hours to run. When I applied the non standard join (and tidied up a few other bits), it took about an hour. Think they had about 1m records to process - so perhaps 8 hours for yours, but just a guess, no promises


    Non standard joins cannot be used in the query builder- but you can create the basic query using the builder then go into sql view and change the joins from = to >= and <= as required.

    Assuming your x and y's are doubles, something else that might improve performance is to change x and y to long's - multiply by a suitable factor so you don't lose granularity. Doubles use 8 bytes, longs 4 - so simplistically, longs will be twice as fast as doubles.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    another way that might ultimately be quicker - since ring1 is a subset of ring2 and ring2 of ring3, create a table of ring3 values for each geoid (not ring1 as you are currently doing). Then run the analysis query off that. You can do a join on geoid from your original table to the new table which will significantly reduce the number of records that need to be analysed to determine ring1 and ring2. Your new table will be pretty huge (much more than 8.6m records) but should be OK in access if there are not too many other fields.

  9. #9
    hci_jon is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2019
    Location
    Montgomery, IL
    Posts
    9
    Looking at your query again, I agree with Ajax that a rewrite of your query might be in order. In your current configuration, it has to run extremely large subqueries 3 times for every line of the main query.

    Have you tried Ajax's method? That should take far less time than the original query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-01-2018, 09:00 AM
  2. Replies: 12
    Last Post: 11-03-2016, 09:22 AM
  3. Replies: 4
    Last Post: 11-24-2015, 03:39 PM
  4. Running SELECT query in MSACCESS
    By dolovenature in forum Programming
    Replies: 4
    Last Post: 09-12-2012, 06:16 AM
  5. Select query for running totals
    By asawadude in forum Queries
    Replies: 2
    Last Post: 10-07-2010, 03:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums