Results 1 to 10 of 10
  1. #1
    lburch is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4

    Export query taking over an hour

    Hi,



    I have a DB with 1.4million records and run 4 queries via sql to get the data i need. i then export to excel and combine into 1 workbook.
    My issue is it is taking over an hour to export. ( all that is above is done by one button on a form)

    The query only generates about 2000 rows and 800 columns of data in the excel file. And it is all text so no formulas or the like.

    I have primary keys and a new laptop with 8mb ram and i5 processor (not super powerful but should handle a simple DB)

    Are there any tricks/tips/formatting suggestions to speed up this excel export?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,817
    My understanding that an Excel workbook with that much data virtually impossible to work with due to poor performance.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lburch is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    The query only generates about 2000 rows and 800 columns of data. And it is all text so no formulas or the like.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,817
    Then it does seem odd to take so long. Does query open fast? What is export code?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    lburch is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    It does generate reasonably quick, a lot quicker than the hour. I'll have to post code later as its on another computer.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,817
    Or maybe it's not so odd. I've never had to work with that much data.

    Best approach might be to write records to a 'temp' table then export the table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    lburch is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    4
    Ok i'll wait till someone has had the issue and found a way to solve/improve it.

  8. #8
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,970
    queries can be slow if among other things:

    you don't have indexing for the fields used for joining, criteria, sorting - note indexes cannot be used if you use an initial * with Like or using mid or right
    you use dlookup or other domain functions
    poorly designed table structure

    It does generate reasonably quick, a lot quicker than the hour.
    I'm assuming your queries are select queries - so how long does the query actually take to run? When you open it, the first few records will usually be displayed very quickly, but the query has not completed until the recordcount box at the bottom is updated - so how long does that take?

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    800 columns?? Just curious is someone looking at this data in excel or is it used to feed some other process?


  10. #10
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,970
    didn't notice the 800 columns - guess that is 4 queries of 200 columns each?

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

Similar Threads

  1. Replies: 3
    Last Post: 04-27-2016, 12:21 PM
  2. Replies: 6
    Last Post: 07-25-2014, 09:25 AM
  3. Query help - Totals by Delivery Method by Hour
    By jdrumgold1983 in forum Queries
    Replies: 3
    Last Post: 05-06-2014, 02:25 PM
  4. Replies: 1
    Last Post: 10-20-2013, 10:04 AM
  5. Query NOT taking the two parameters WHY?
    By iamraja5 in forum Forms
    Replies: 7
    Last Post: 03-16-2010, 04:29 AM

Posting Permissions

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