Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Query running VERY slow

    I'm fairly unfamiliar with Access but am an avid Excel user. I had to recently switch a function I'm doing to Access due to the record size. The issue I'm having is the queries I've set up are running painfully slow, as in hours. I'm not sure what I'm doing wrong. I have 3 tables with records of about 2 million, 7 million and 55K respectively. My first question is, is it the size that is my issue?

    I'm querying the 3 tables based on 2 matching criteria (contract ID and sku) and bringing in all fields from table 1 and 1 field each from tables 2 and 3. I'm then summing the 1 field from each of the 3 tables. Can someone help me speed this up? I'm indexing fiscal month, fiscal year, contract ID and sku. Any assistance would be greatly appreciated! I've tried deleting unnecessary columns from my tables, eliminating multiple queries, etc, but nothing seems to help.

    SELECT [Rpt 1 Rev].[Fiscal Month ID], [Rpt 1 Rev].[Fiscal Year ID], [Rpt 1 Rev].[Contract ID], Sku_Matrix.Term, Sum([Rpt 1 Rev].[DRBA]) AS [SumOfDRBA], Sum([Rpt 2 Rev].[ERA]) AS [SumOfERA], Sum([Rpt 3 Rev].[DRBA]) AS [SumOfDRBA], Sum(nz([Rpt 1 Rev]![DRBA],0)-nz([Rpt 2 Rev]![ERA],0)-nz([Rpt 3 Rev]![DRBA],0)) AS Net INTO [Net_Detail_Rev Table]
    FROM (([Rpt 1 Rev] INNER JOIN Sku_Matrix ON [Rpt 1 Rev].Sku = Sku_Matrix.Sku) LEFT JOIN [Rpt 2 Rev] ON ([Rpt 1 Rev].Sku = [Rpt 2 Rev].Sku) AND ([Rpt 1 Rev].[Contract ID] = [Rpt 2 Rev].[Contract ID])) LEFT JOIN [Rpt 3 Rev] ON ([Rpt 1 Rev].Sku = [Rpt 3 Rev].Sku) AND ([Rpt 1 Rev].[Contract ID] = [Rpt 3 Rev].[Contract ID])


    GROUP BY [Rpt 1 Rev].[Fiscal Month ID], [Rpt 1 Rev].[Fiscal Year ID], [Rpt 1 Rev].[Contract ID], Sku_Matrix.Term;

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    how many fields are in your tables?

    I haven't worked with millions of lines in access but I have worked with hundreds of thousands that have 150+ fields.

    Can you say exactly what you're trying to accomplish with this query? I'm curious why you would need to create a new table that was (by the looks of it) just storing some summary data.

    Have you tried just running a select query without the INTO statement to see if that runs any more quickly?

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried to optimize the query by using say, 1000 to 10000 records from the tables and see if there is some issue.

    Data volume could be an issue, but hours seems extreme.

    I agree with rpeare -Why are you doing the select into?

  4. #4
    Join Date
    Jul 2011
    Posts
    2
    I have not tried running for smaller datasets at this time but I have noticed the queries have gotten progressively slower as the data sen has grown. Buy I've also had to tweak the queries over this tr as new variables came into play. My intent is that I have three reports that have three different related numbers for a single contract id that I need to combine into a single net number. So my query is giving me rpt 1 amount - rpt 2 amount - report 3 amount. I then have a detailed contract total for each month. I then also run a cross tab query on that to create a summary of that data by original sale date and contract term. That is my ultimate goal.

    I'm not sure how to run for a smaller dataset now. Everything I do in this database is slow. Imports, exports and delete queries.

  5. #5
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25
    Sometimes when I have table and other objects split into 2 DBs (client and server) running through network traffic the response can be slow. Running a query for hours is quite extreme, but I usually kill it after 5 min (I'm not that patient), sometimes the issue could be insufficient CPU % or temporary space allocated to your virtual test environment.

    Do you have unique indices for the join fields? That will help the performance for million rows.

    One approach I usually do to diagnose a complicated queries (more than 3 tables of two joins and with complicated functions) is to create stepwise queries of smaller joins and build upon that. A sudden increase of run time indicates issues during that step. If the response time is bad with the 1st query of 2 tables, you know it's not caused by your query.

    Hope that helps.

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

Similar Threads

  1. Query with a running sum
    By is49460 in forum Queries
    Replies: 3
    Last Post: 09-07-2013, 11:11 PM
  2. Slow query - Help to speed up?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 05-26-2011, 01:23 PM
  3. Running a query
    By scarlettera in forum Queries
    Replies: 3
    Last Post: 04-04-2011, 01:59 PM
  4. Replies: 2
    Last Post: 01-19-2011, 10:19 AM
  5. Replies: 0
    Last Post: 02-09-2007, 09:20 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
  •  
Other Forums: Microsoft Office Forums