Results 1 to 13 of 13
  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    Just wondering about deleting data after running query

    Hello all,



    I have a table that is getting very slow to update, I am assuming due to it's size. I got thinking maybe have another table with the same fields and using that for daily entry data and at the end of the day run a query to append the data to the "Master" table and then delete the data in the "Daily" table.

    Does anyone do anything similar to this and should it work?

    Thanks in Advance

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Dave14867 View Post
    Hello all,

    I have a table that is getting very slow to update, I am assuming due to it's size. I got thinking maybe have another table with the same fields and using that for daily entry data and at the end of the day run a query to append the data to the "Master" table and then delete the data in the "Daily" table.

    Does anyone do anything similar to this and should it work?

    Thanks in Advance
    How many fields and how many records in the table, approx
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Bob,

    Maybe 30 fields and currently over 33000 records

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Dave14867 View Post
    Hello all,

    I have a table that is getting very slow to update.........
    Tell us more about this. What exactly are you doing. How long is very slow.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I have a form that contains a subform. I enter data in the form and save it, that data gets saved to a table called tblWorkOrders, then a query is ran that looks up info in another table and that info is then appended to tblInventoryTransactions and is then populated in the subform. It used to happen very quickly, when you clicked the command button to save it would happen almost instantly, not it can take a minute or 2 (haven't actually timed it) but it is considerable time. I have tried some other methods to rule out things and it seem as though it is the size of the tblInventoryTransactions that is causing it.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    ....then a query is ran that looks up info in another table and that info is then appended to tblInventoryTransactions and is then populated in the subform....
    I think I'd check out your table structure and table indexing.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Bob,

    I played with the indexing and that didn't seem to help. Right now I am in process of trying out my method to just see what happens. I have to make some other changes to Queries and forms so it may take me a while, of course I am doing all this on a copy of the original database.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If your query or queries in a stack contain things like calculated fields using aggregate functions, IIF expressions, function calls, grouping, etc. this can really slow things down and often won't become evident until the record count increases. Such things can cause Access to have to cycle through all the records for each thing, which can cause an exponential slow down. 33K records is not much; even for your field count. If you think it's the table, it's easy enough to test. Run a simple select query against it such as SELECT * FROM tblMyTable; and you'll likely find it's instantaneous. That would indicate the table isn't the problem.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    There are some fields that I can get rid of, thought they were needed when designing but haven't been used at all when final design was completed so I will look at getting rid of those. The queries do contain some calculations and grouping, I tried to eliminate this as the problem and it still seems to exist so I think the only thing left is the large table. How much can unused fields affect performance?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    How much can unused fields affect performance?
    If they're not calculated? Probably not much at all, but if part of a grouping, perhaps you'll notice a difference. Only one way to tell. If you're storing these calculations, then you shouldn't. Your db design approach may be the major factor.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I am storing the value of the calculation only because it is the only way I can make things work. Lets say I am making 20 pcs of item A and it uses 4 of item B for each Item A, tblInventoryTransactions has a field for UnitsUsed and that contains the result of a query calc that takes the 20 pcs being made of item A, multiplies that by the 4 ea of item B for a result of 80, that qty gets stored in UnitsUsed for that row of data, there is also a bunch of other info stored in that row like WO#, lot#, etc. Then if I look up the DHR # that everything is recorded under, the subform displays all of the relevant info like PN, UnitsUsed, Lot#, etc. The value isn't used to keep track of Inventory on this form, the form that does calculate Inventory Qty isn't an issue, and that looks at the same tblInventoryTransactions.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am storing the value of the calculation only because it is the only way I can make things work
    I'll have to take your word on that as I don't see why but it doesn't matter. If you find that you need to alter the source value so that 20 becomes 21, then what do you have to do? If the answer is redesign your queries/forms/reports/code then hopefully you are correct in your assessment. Otherwise, you're in for more trouble ahead.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    33000 records is not many and should not impact on performance. I would stop focusing on number of records/fields and focus on the overall design and process. We have precious little to go on but comments like

    there is also a bunch of other info stored in that row like WO#, lot#, etc.

    implies a design flaw. And as others have said, with very few exceptions (and I accept this might be one of them) storing calculated values is generally a bad idea.

    Perhaps if you provide detail of your table design/relationships and the sql to your update? append? queries we might be able to suggest a better solution.



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

Similar Threads

  1. Replies: 4
    Last Post: 02-24-2017, 10:16 AM
  2. Replies: 7
    Last Post: 01-30-2013, 09:47 AM
  3. Wondering if this automation is possible?
    By Macguy2125 in forum Import/Export Data
    Replies: 1
    Last Post: 05-24-2012, 01:04 PM
  4. Replies: 8
    Last Post: 02-03-2011, 09:51 AM
  5. Replies: 11
    Last Post: 12-14-2010, 01:25 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