Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cascade delete won't (A2K SR1)

    I have two tables set up in a database, TblAvailDates and TblSalesHist -- a short reference file of data dates in the database, and the full data table. The tables are set up with a many-to-one, cascading relationship, whereby if a date is deleted from the first table, all related data from the main table would be deleted too.

    We had planned to purge old data from the database by deleting the date from the reference table and allowing the cascading delete to handle the rest. When the database was initially set up the cascade delete would work, but in testing today we would get out of memory errors (can't undo). We would click "yes" to continue, but the delete would halt, leaving the date in the small table and all the data in the large. The database only has approximately 400,000 records in it, 10,000 per week, but it is also indexed on a number of fields. (We anticipated the slow updates when adding records, but needed the quick search times gained from having these indexes.) The purges are intended to keep the database trimmed to one year's worth of data.

    The machines that the database is running on are Pentium 2's, 64MB RAM, and running with relatively little overhead load; 90% resources were free. Win98 is managing the swap file, with gigabytes of space available. I can't think of any reason we should have been receiving any memory errors.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cascade delete won't (A2K SR1)

    David,

    There's no *only* to 400,000 records. For Access, that's a lot of data, especially if it's in a single table. 64MB of RAM is not really very much for dealing with Access, and especially for that big a database. As I recall, for Office 2000, that's the bare minimum memory recommended, and we all know that minimum means if you want it to perform you have to double that. Once you start trying to run the delete, I would suspect that resource number dropped down to close to nothing, which is typically what happens when Access is running queries anyhow, even on machines with a lot more memory.

    As to your question, I don't understand this:
    <hr>The tables are set up with a many-to-one, cascading relationship, whereby if a date is deleted from the first table, all related data from the main table would be deleted too<hr>
    You have to enable referential integrity in order to enable cascading deletes, and you can't do that from the many side of a relationship. Which table is the one side of the relationship? I assumed it was tblAvailDates. And how many is many? Are there thousands of records for that date, tens of thousands, or what? Cascading deletes isn't really intended to do large scale housekeeping, but it should handle a reasonable number of records without problems. The real question is, can you delete all those records with a straight delete query? If it breaks down as well, then the problem isn't the cascade, it's the resources on your machine. I've seen more powerful machines brought to their knees by big action queries and had to resort to a dual processor machine for some large queries that involved hundreds of thousands of records.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cascade delete won't (A2K SR1)

    <P ID="edit" class=small>Edited by d_rasley on 03-Jan-02 08:17.</P>All of the main data is in one table, accumulating at around 10,000 records of 40+ fields per week. This is why we do not want the table to keep ballooning as new data is added beyond one year... Add a week, count the number of weeks, and if >52, delete the earliest data... That was our plan, and it still is, though we now know that the process will need to be a little more detailed than just deleting one record from the date control table.

    Yes, the One-to-Many relationship (pardon the dyslexia <img src=/S/blush.gif border=0 alt=blush width=15 height=15>) is from the short date table to the large data table, one record matching up to ~10,000. We had tested the cascade delete early on, while in development and early implementation stages, and it worked fabulously. We had tested the report retrieval with a full year's worth of simulated data. We just overlooked this one teensy little detail. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    We tried a straight delete query and it managed to bog down as well. I've suggested to the other developer that we should try a sequence of delete queries, matching the date with another variable to whack the data a section at a time instead of trying to do it all at once.

Posting Permissions

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