Results 1 to 10 of 10
  1. #1
    Sparkydog is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    18

    Query is fast to view on screen but slow when exported or fed to a report


    I'm using 2010 and have a database formatted .accdb but it is nothing but linked tables to a SQL database. Been using it for years and never seen this unique set of symptoms. I have a query that when run by itself to just view the data on screen runs instantly and shows all the results - which are only a few hundred records. (Not the same as restricting the preview to only a 100 records.) What I'm trying to say is that the query processes through all the database records and returns the results which are only a few hundred records in a matter of seconds.

    If I run the same query to export to excel with the same criteria - the few hundred records takes 10-15 minutes to export.

    If I feed the query to a report and preview the report it again runs slow - taking the same 10-15 minutes to finish running and appear on the screen.

    I have googled for this phenom and didn't find anything helpful. I have searched in three sections of this forum: Access, Import/export and queries. Yes I get a lot of threads but again nothing with this weird combo of fast query that slows for export or reporting.

    Thanks for any ideas.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Transferspreadsheet should be just as fast as the query:
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,sQry, sFile, true, sSheetName

    is this what you are using?

  3. #3
    Sparkydog is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    18
    I should probably also mention that the SQL server is running on a box with MS server 2016 op system and the ODBC connection for the linked tables is using ODBC driver 11 for SQL Server. I have a hunch this issue started occurring as we upgraded server op system from server 2008 to 2016. (maybe)

  4. #4
    Sparkydog is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    18
    No I am right clicking on the query name and selecting "export" then "excel" yada yada

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you sure the query is that fast? It will often show the first page of results right away but not actually be done. After it opens, try to scroll to the last record. Is it still fast? Are all fields showing? If so, what's the SQL of the query (looking for any functions that SQL Server wouldn't understand)? This may be a case where converting to a pass through query would solve the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Sparkydog is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    18
    Quote Originally Posted by pbaldy View Post
    Are you sure the query is that fast? It will often show the first page of results right away but not actually be done. After it opens, try to scroll to the last record. Is it still fast? Are all fields showing? If so, what's the SQL of the query (looking for any functions that SQL Server wouldn't understand)? This may be a case where converting to a pass through query would solve the problem.
    Yes that's a good question - I am sure. I have gone to the last record in order to make sure I was not just looking at the first page results. Going to the last record is nearly instantaneous and that's also how I know that there are only a few hundred records in the query result.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm with ranman then, exporting shouldn't take much longer than opening the query. Do other queries export quickly? I haven't used that particular method, I've used the External Data tab, but I don't see how that would have an affect. That's a massive time difference; something funky is going on. You might try a pass through query just for chuckles.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Sparkydog is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    18
    Quote Originally Posted by pbaldy View Post
    I'm with ranman then, exporting shouldn't take much longer than opening the query. Do other queries export quickly? I haven't used that particular method, I've used the External Data tab, but I don't see how that would have an affect. That's a massive time difference; something funky is going on. You might try a pass through query just for chuckles.
    All other queries in this database (which I have used for years) take roughly the same amount of time to preview on screen as they do when viewed through a report or to export. This is the first time and only query that I have run into this phenomenon.

  9. #9
    Sparkydog is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    18
    What the heck - here's the code:

    Final query is a query of other queries.

    SELECT [Production Review1 Query].Work_Date, [Production Review1 Query].Name, [Production Review1 Query].Shift_Name, [Production Review1 Query].Job, [Production Review1 Query].Part_Number, [Production Review1 Query].PartDesc, [Production Review1 Query].Work_Center, [Production Review1 Query].Sequence, [Production Review1 Query].OpDesc, [Production Review1 Query].Est_Setup_Hrs, [Production Review1 Query].Act_Setup_Hrs, [Production Review1 Query].Act_Setup_Labor_Hrs, [Production Review1 Query].Est_Run_Hrs, [Production Review1 Query].Act_Run_Hrs, [Production Review1 Query].Act_Run_Labor_Hrs, [Production Review1 Query].Act_Run_Qty, [Production Review1 Query].Act_Setup_Qty, [Production Review1 Query].Act_Scrap_Qty, [Production Review1 Query].EstRunRate1, [Production Review1 Query].Run_Method, [Production Review1 Query].ActlRunRate, [Production Review1 Query].[Display Hrs], [Production Review1 Query].[Parts/Hr], [Production Review1 Query].[Hrs/Part], IIf([estrunrate1]=0,0,IIf([run_method]="fixedhrs",[estrunrate1],IIf([run_method]="parts/hr",[estrunrate1],IIf([run_method]="min/part",60/[estrunrate1],IIf([run_method]="sec/part",3600/[estrunrate1],IIf([run_method]="hrs/part",1/[estrunrate1],0)))))) AS EstRunPPH
    FROM [Production Review1 Query]
    WHERE ((([Production Review1 Query].Work_Date) Between [Start Date] And [End Date]))
    ORDER BY [Production Review1 Query].Name, [Production Review1 Query].Job, [Production Review1 Query].Sequence;

    And here's the code for the other queries.
    Production Review1 Query:
    SELECT [Production Review2 Query].Part_Number, [Production Review2 Query].PartDesc, [Production Review2 Query].Work_Center, [Production Review2 Query].Job, [Production Review2 Query].Sequence, [Production Review2 Query].OpDesc, [Production Review2 Query].Shift_Name, [Production Review2 Query].Name, [Job Run-Setup Info Query].Est_Setup_Hrs, [Production Review2 Query].Act_Setup_Labor_Hrs, [Production Review2 Query].Act_Setup_Hrs, [Job Run-Setup Info Query].Est_Run_Hrs, [Production Review2 Query].Act_Run_Hrs, [Production Review2 Query].Act_Run_Labor_Hrs, [Production Review2 Query].Act_Run_Qty, [Production Review2 Query].Act_Setup_Qty, [Production Review2 Query].Act_Scrap_Qty, [Production Review2 Query].Work_Date, [Job Run-Setup Info Query].EstRunRate, [Job Run-Setup Info Query].Efficiency_Pct, [Production Review2 Query].[Display Hrs], [estrunrate]*[efficiency_pct]/100 AS EstRunRate1, [Job Run-Setup Info Query].Run_Method, IIf([parts/hr]=0,0,IIf([run_method]="fixedhrs",[act_run_hrs],IIf([run_method]="parts/hr",[parts/hr],IIf([run_method]="min/part",60/[parts/hr],IIf([run_method]="sec/part",3600/[parts/hr],IIf([run_method]="hrs/part",1/[parts/hr],0)))))) AS ActlRunRate, [Production Review2 Query].[Parts/Hr], [Production Review2 Query].[Hrs/Part]
    FROM ([Production Review2 Query] LEFT JOIN [Job Run-Setup Info Query] ON ([Production Review2 Query].Sequence = [Job Run-Setup Info Query].Sequence) AND ([Production Review2 Query].Job = [Job Run-Setup Info Query].Job)) INNER JOIN [CNC Work Centers] ON [Production Review2 Query].Work_Center = [CNC Work Centers].[CNC WC];

    Production Review2 Query:
    SELECT [Production Review Labor Query].Part_Number, [Production Review Labor Query].PartDesc, [Production Review Labor Query].Job, [Production Review Labor Query].Sequence, [Production Review Labor Query].Job_Operation, [Production Review Labor Query].Operation_Service, [Production Review Labor Query].OpDesc, [Production Review Labor Query].Work_Center, [Production Review Labor Query].Work_Date, [Production Review Labor Query].Shift_Name, [Production Review Labor Query].Name, [Production Review Labor Query].Act_Run_Labor_Hrs, [Production Review Labor Query].Act_Run_Hrs, [Production Review Labor Query].Act_Setup_Labor_Hrs, [Production Review Labor Query].Act_Setup_Hrs, [Production Review Labor Query].[Display Hrs], [Production Review Labor Query].Act_Run_Qty, [Production Review Labor Query].Act_Setup_Qty, [Production Review Labor Query].Act_Scrap_Qty, IIf([act_run_labor_hrs]>0,([act_run_qty]+[act_setup_qty])/[act_run_labor_hrs],0) AS [Parts/Hr], IIf([act_run_qty]>0,[act_run_labor_hrs]/([act_run_qty]+[act_setup_qty]),0) AS [Hrs/Part]
    FROM [Production Review Labor Query]
    ORDER BY [Production Review Labor Query].Part_Number;

    Production Review Labor Query:
    SELECT dbo_Job.Job, dbo_Job.Status AS JobStat, dbo_Job.Part_Number, dbo_Job.Description AS PartDesc, dbo_Job_Operation.Sequence, dbo_Job_Operation.Job_Operation, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Work_Center, dbo_Job_Operation.Description AS OpDesc, dbo_Job_Operation.Status, IIf(Weekday(Now()) Between 3 And 7,1,2) AS DOW, dbo_Job_Operation_Time.Work_Date, dbo_Shift.Shift_Name, dbo_Job_Operation_Time.Employee, [last_name]+", "+[first_name] AS Name, dbo_Employee.First_Name, dbo_Employee.Last_Name, dbo_Job_Operation_Time.Act_Setup_Labor_Hrs, dbo_Job_Operation_Time.Act_Setup_Hrs, dbo_Job_Operation_Time.Act_Run_Labor_Hrs, dbo_Job_Operation_Time.Act_Run_Hrs, [dbo_job_operation_time]![act_setup_labor_hrs]+[dbo_job_operation_time]![act_run_labor_hrs] AS [Display Hrs], dbo_Job_Operation_Time.Act_Run_Qty, dbo_Job_Operation_Time.Act_Setup_Qty, dbo_Job_Operation_Time.Act_Scrap_Qty, dbo_Job_Operation_Time.Setup_Labor_Rate, dbo_Job_Operation_Time.Run_Labor_Rate
    FROM (((dbo_Job_Operation INNER JOIN dbo_Job_Operation_Time ON (dbo_Job_Operation.ObjectID = dbo_Job_Operation_Time.Job_Operation_OID) AND (dbo_Job_Operation.Job_Operation = dbo_Job_Operation_Time.Job_Operation)) INNER JOIN dbo_Employee ON dbo_Job_Operation_Time.Employee = dbo_Employee.Employee) LEFT JOIN dbo_Shift ON dbo_Employee.Shift = dbo_Shift.Shift) INNER JOIN dbo_Job ON dbo_Job_Operation.Job = dbo_Job.Job
    ORDER BY dbo_Job.Job, dbo_Job_Operation.Sequence, dbo_Job_Operation_Time.Work_Date;

    All of the objects in that final query (above) are tables in the SQL database.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    General thoughts that may or may not be relevant. I'm grasping at straws that wouldn't seem necessary given how fast the query returns all records.


    • Can the criteria be applied to the base query instead of the final query? Generally the earlier you restrict records the better.
    • What version of SQL Server? IIf() was not included there until 2012, so might force Access to do more processing than it should.
    • I'd drop the ORDER BY clauses from all but the final query. They would just slow down the others and the order has no meaning until the end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Slow screen refresh rate
    By razors1968 in forum Access
    Replies: 7
    Last Post: 07-22-2014, 01:26 PM
  2. Replies: 1
    Last Post: 04-24-2013, 08:51 PM
  3. Slow on 2010 but fast on 2007?
    By Jaffacaique in forum Queries
    Replies: 5
    Last Post: 09-09-2012, 04:16 AM
  4. Replies: 5
    Last Post: 07-31-2012, 06:15 PM
  5. Replies: 2
    Last Post: 12-16-2010, 02:46 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