Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    table size (Access 2000)

    I'm a new user and would like to improve the query run times on a table that currently has 336,000 rows. The database currently holds journal entry adjustments for each period. Should I create new tables one for each month? One for each quarter?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: table size (Access 2000)

    Have you got appropriate indexes on the tables behind the query.

    Indexes make a big difference to performance.

  3. #3
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table size (Access 2000)

    I have indexes on 2 of the fields. The queries take 45 minutes to run even with the indexes. Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table size (Access 2000)

    Welcome to Woody's Lounge!

    Where is your database stored? On a local hard disk or on a network drive? If you're processing that many records, you should always work locally. If necessary, copy the database to a local disk, then execute the query, and finally copy the database back to the network.

  5. #5
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table size (Access 2000)

    Yes, I am working locally. Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table size (Access 2000)

    Does the query act on all records, or on part of the records? Or could it be made to act on only part of the records?

  7. #7
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table size (Access 2000)

    Yes the query acts on all the records. The query is running off of another query that has a join.

    The first query SELECT [Summary Data Query].*, [Formatting BD Table].Desc
    FROM [Summary Data Query] LEFT JOIN [Formatting BD Table] ON [Summary Data Query].[Entry Date] = [Formatting BD Table].[Entry Date];

    The second query SELECT [All 3CPO Data Query].PKG_NUM, [All 3CPO Data Query].PROC_STRT_DT_TM, [All 3CPO Data Query].PROC_END_DT_TM, [All 3CPO Data Query].[Entry Date], Format([Entry Date],"mm/yyyy") AS [Entry Month], [All 3CPO Data Query].Desc AS BD, [All 3CPO Data Query].System, [All 3CPO Data Query].[Logical Source], [All 3CPO Data Query].CAL_DT AS [Eff Date], [All 3CPO Data Query].DATA_SOURCE_CODE, Count([All 3CPO Data Query].DATA_SOURCE_CODE) AS Adjustments, [All 3CPO Data Query].ADJ_RSN_CD, [All 3CPO Data Query].ADJ_RSN_DESC, Sum([All 3CPO Data Query].US_DOL_BAL_AMT) AS Dollars, Sum([All 3CPO Data Query].ABS) AS [Dollars ABS]
    FROM [All 3CPO Data Query]
    GROUP BY [All 3CPO Data Query].PKG_NUM, [All 3CPO Data Query].PROC_STRT_DT_TM, [All 3CPO Data Query].PROC_END_DT_TM, [All 3CPO Data Query].[Entry Date], Format([Entry Date],"mm/yyyy"), [All 3CPO Data Query].Desc, [All 3CPO Data Query].System, [All 3CPO Data Query].[Logical Source], [All 3CPO Data Query].CAL_DT, [All 3CPO Data Query].DATA_SOURCE_CODE, [All 3CPO Data Query].ADJ_RSN_CD, [All 3CPO Data Query].ADJ_RSN_DESC;

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: table size (Access 2000)

    Do you really need to group on all those fields in that second query?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table size (Access 2000)

    I think this will be a heavy load for Access - a left join is slower than an inner join, and your second query groups on 12 fields, among which a calculated field Entry Month.

    Do you have an index on Entry Date in the appropriate tables?

  10. #10
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table size (Access 2000)

    Yes I have an index on Formatted Date which is a key field.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table size (Access 2000)

    I asked about the field named Entry Date since that is the join field in the first query.

  12. #12
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table size (Access 2000)

    I don't know if I'm saying this correct but entry date was created using the formatted date field and then changing the format the line is below for the entry date. So I'm not sure if I can index this. Is this correct?
    Entry Date: Format([Formated Date],"mm/dd/yyyy")

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table size (Access 2000)

    You can't put an index on a field in a query. Why are you using formatted text fields for dates?

  14. #14
    New Lounger
    Join Date
    Nov 2007
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table size (Access 2000)

    I need to match the dates with the business day of the close. For example 01/02/2007 would be bd 1 01/02/2007 bd 2...

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: table size (Access 2000)

    Sorry, I don't understand that.

Page 1 of 2 12 LastLast

Posting Permissions

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