Archiving or Date Compression of large tables

ObiWan
ObiWan Member Posts: 21
edited 2009-01-12 in SQL Performance
Hi, happy new year 8)
We have an pretty large db on SQL (approx 330 gig). And after about a year of postings we are looking into either archiving (moving) or std. Date Compression i NAV.
Its now primarely the AdjustCost routine that is taking too much time...

Has anyone any suggestions about how these things are solved? ex moving data to new tables or new tables in another db or simply use std date compression.
There are not any customization on the huge tables like 17,32 and 5802. (Only index optimizing).


Best regards

ObiWan

Comments

  • ara3n
    ara3n Member Posts: 9,258
    What Costing Method are you using?

    I have a client who is on FIFO and they wrote adjust cost to run as SQL job. They wrote their own adjust cost as stored procedure.

    We have a client with similar issue. Average costing We are planning to do similar process for them as well.

    Have you split the Adjust cost and cost post to GL into separate processes?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ObiWan
    ObiWan Member Posts: 21
    Hi, they are using FIFO all over.

    And yes I have split the cost posting to G/L into an separate customized version of the std. which uses same DocNos as in ValueEntry but
    doing the posting "Per Posting Group". Then we still have the same link to GL but a lot less entries.

    Building a routine as an SP seems smart.
    That might perform a lot better, but could be somekinda time consuming.
    Are the whole routine been written as an SP or only to gather data (summarize and stuff)?

    I have been using LinkedObjects for gathering some of the data. Then still keeping the bizlogic inside NAV.

    There are approx. 280 locations (stores) so no of records in 5802 are now just below 40.000.000.
    The records that are made from Adjustments make about 25%.
    I believe that we might need to do somekind of storage/compression anyway.

    We`re using HP-EVA SAN as storage and HP Itanium 64 with SQL 2005 as db server.
    NAV is 5.0
    The application is performing quite well, but I feel that the db growth is too huge.
    Maintenance Plans are beginning to take a lot of time.


    Best regards

    ObiWan
  • bbrown
    bbrown Member Posts: 3,268
    I'd be interested in hearing moe about how you decide to approach this issue. I'm working with a customer that has a smaller database but similar value entries. Their DB is around 150 GB and growing. Value entry is about 32 million records. They add about 2 million records a month. GL is around 55 million records.

    Curently Adjust Cost is run nightly and takes 60 to 90 minutes on a typical night. There will be some days, due to the nature and volume of activity, where adjust cost may take upwards of 3 or 4 hours. As the plant gets busier there will be less and less time to run this process. I know at some point I will likely face this same issue.
    There are no bugs - only undocumented features.

Categories