Options

Delete Past Records

roshandilrukroshandilruk Member Posts: 51
Hello Pals,

Our Nav database has another module called POS. That is what the main application which we use to do sales. There are two main tables, POS Header and POS Lines. They are more over similar to Sales Inv. Header and Sales Inv. Line tables. The tables are related with their Primary Keys.

The issue is, the database is getting much bigger. We find it hard to delete the old records from the POS Header and POS Line tables as it consumes such a long time. We have written number of programs to Delete past data, when we try to delete for a longer period (a month or more) , it takes a long time for the delete process and finally throw an error with no sufficient space. However, even if we expand the database by 10 GB, still the issue remain the same. This also slowdown the entire database.

Does anybody have an idea to write a Delete Program to flush the history data, but more efficiently while it consumes less time and resourses?

Thanks

Answers

  • Options
    bbrownbbrown Member Posts: 3,268
    A few questions:

    1. SQL or Native?

    2. How large is the database and how much free space?

    3. Why are you deleting these records in the first place?
    There are no bugs - only undocumented features.
  • Options
    roshandilrukroshandilruk Member Posts: 51
    It's Native Database

    At the moment the size is 90GB

    We do not need to keep the Data except for the last couple of years, but the database has data for last 6-7 years.

    Reason : The licence only permits to expand the databse up to 130GB. As its already 90GB, the data has to be deleted to save the space.
  • Options
    bbrownbbrown Member Posts: 3,268
    It's Native Database

    At the moment the size is 90GB

    We do not need to keep the Data except for the last couple of years, but the database has data for last 6-7 years.

    Reason : The licence only permits to expand the databse up to 130GB. As its already 90GB, the data has to be deleted to save the space.

    You can update the license to 256 GB (no cost).
    There are no bugs - only undocumented features.
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    It's Native Database

    At the moment the size is 90GB

    We do not need to keep the Data except for the last couple of years, but the database has data for last 6-7 years.

    Reason : The licence only permits to expand the databse up to 130GB. As its already 90GB, the data has to be deleted to save the space.


    You should consider moving to SQL. I know its a big job, but this issue is only going to get more and more complex, and the longer you leave it the more difficult it will be to swap.

    In terms of data growth during delete, you could encapsulate a particular group of consistent records, and then use a commit between each delete.
    David Singleton
  • Options
    roshandilrukroshandilruk Member Posts: 51
    I kind of written a code which works with lesser time than the previous times. Thanks for your help.

    Cheers!
Sign In or Register to comment.