Date Compression

matias_jaure
matias_jaure Member Posts: 157
edited 2007-09-20 in NAV/Navision Classic Client
Hello! I need to compress G/L Entry and Ledger Entry Dimension. We have a 80 GB SQL Database in NAV4.0 and these two tables have 23Gb each one.
This is because the deferral recognition that they used, now we are about to change this procedure and we want to reduce old data with the Data Compression functionallity. We only want to compress these two tables.
I run Date Compress but it run for three days and then gave a log overflow error. The log reach 100 Gb, may be I only need to make it bigger.
The problem is that I really don't know how to configure this compression, because Navision ask me to retain some Dimensions that I don't really care. Also I don't have any good documentation to read. Any advice could help us!... thank you very much for your time!

Comments

  • mstallmann
    mstallmann Member Posts: 138
    I would suggest running compression for shorter periods. You will need to run compression several times, but it should get you past the issues you are encountering.
    www.Oztera.com
  • DenSter
    DenSter Member Posts: 8,307
    If more than half your database is made up by these two tables then those tables are probably very badly over indexed. What you need to do is tune the indexes first and see how big the tables are then.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • matias_jaure
    matias_jaure Member Posts: 157
    DenSter wrote:
    If more than half your database is made up by these two tables then those tables are probably very badly over indexed. What you need to do is tune the indexes first and see how big the tables are then.

    I am not sure how to do that, any advice? or any paper to read about it? I think that it could save a lot of space but I am not sure which index delete.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    matias_jaure wrote:
    Hello! I need to compress G/L Entry and Ledger Entry Dimension. We have a 80 GB SQL Database in NAV4.0 and these two tables have 23Gb each one.
    This is because the deferral recognition that they used, now we are about to change this procedure and we want to reduce old data with the Data Compression functionallity. We only want to compress these two tables.
    I run Date Compress but it run for three days and then gave a log overflow error. The log reach 100 Gb, may be I only need to make it bigger.
    The problem is that I really don't know how to configure this compression, because Navision ask me to retain some Dimensions that I don't really care. Also I don't have any good documentation to read. Any advice could help us!... thank you very much for your time!

    Hi Matais,

    As a freelancer, one of my great sources of work is to fix up a database that was damaged by someone using the date compression routines. These routines are designed to run on a totally unmodified database, but almost never run on a modified system. (Did you have any mods made to your System?)

    In your case, follow Daniel's advise, you need to take a close look at your whole implementation, and how the data is being built, and how you are using it.

    And yes you can read papers and search the internet, but where you are now, (46 Gig in 2 tables) your best solution is to get someone like Daniel to help you out.
    David Singleton
  • matias_jaure
    matias_jaure Member Posts: 157
    ok, May be you could help me to decide with this information:

    In G/L Entry Table I have the following keys:

    Key
    Entry No.
    G/L Account No.,Posting Date
    G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
    Document No.,Posting Date
    Transaction No.
    Close Income Statement Dim. ID
    IC Partner Code

    and in Ledger Entry Dimension:

    Table ID,Entry No.,Dimension Code
    Dimension Code,Dimension Value Code


    Any suggestion??
  • DenSter
    DenSter Member Posts: 8,307
    Just those keys for the G/L Entry table? I was expecting more than that, are you sure your number of 23GB per table is right? Out of those keys I'd probably only redesign the third one. How about SIFT keys, do you have many sumindexfields in that table? There are probably lots of empty SIFT records.

    The dimension table is probably huge because you are using many dimensions.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • matias_jaure
    matias_jaure Member Posts: 157
    DenSter wrote:
    Just those keys for the G/L Entry table? I was expecting more than that, are you sure your number of 23GB per table is right? Out of those keys I'd probably only redesign the third one. How about SIFT keys, do you have many sumindexfields in that table? There are probably lots of empty SIFT records.

    The dimension table is probably huge because you are using many dimensions.

    Yes, just those keys. These are the SumIndexFields

    for the key:
    G/L Account No.,Posting Date
    SumIndexFields:
    Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount

    and for the key:
    G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
    SumIndexFields:
    Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount

    I have almost 38 million of rows in GL Entry table and a little more than 333 million of rows in Ledger Entry Dimension :shock: , may be it can explain the size
  • DenSter
    DenSter Member Posts: 8,307
    yes close to half a billion records will increase the size of your tables for sure :mrgreen:

    I'd have to take a closer look at your table to know for sure why the G/L Entry table is so big and to see what would improve the performance. In itself the number of keys is not that big, you do have quite a few sumindexfields, but also not huge.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • matias_jaure
    matias_jaure Member Posts: 157
    I will try to explain why GL Entry is so big. Our client has to deferred sells. That is to recognized a part of the sell when the customer buys the product and then, each month, the customer pays the part belonging to the month and system has to recognize that part.
    The people who managed the system before us decide to recognize each month creating an entry for each invoice line. creating thousands miles of entries each month. The process takes 3 hours if we are lucky.
    So, we decided to change this way and, if it possible, we want to compress the old entries, or at least reduce the size of these huge tables.

    I don't know if it is usefull for you, please let me know if you want something else
  • PerJuhl
    PerJuhl Member Posts: 55
    Hi Matias

    Did you get any further with this ?

    For a start you should ONLY compress THE FIRST months.

    Try to retain as few fields as possible, the more fields you
    retain the less effective compression.

    BR Per

    pjc@kh.dk

Categories