Post reply

Transaction Times on Very Large Table

  • January 21, 2009 at 5:10 am

    #196307

    Hello all,

    I am working with a custom designed verion of the Axapta ERP system.

    Within the database, there is a very large table (which has been designed very poorly imo - as it creates a new record every time someone looks at the existing record) which currently has 212million rows.

    Transaction times have historically been fairly slow, but bearable, however recently there have been a number of issues experienced and the transaction times are unbearably slow on any data associated with this particular table.

    I reindex the table on a regular basis, and it temporarily improves matters, but I have seen that the statistics are set to update automatically on the table.

    I believe there should be an improvement by turning this off, and updating the statistics overnight just using an agent job?

    Would anyone be able to advise if this would be the case and what sort of extent it would improve transaction times if it was turned off, and whether there are any other implications to consider about updating the statistics overnight?

    I am looking at removing unnecessary historical data, but suspect that the table will still be around 150m rows.

    Any other suggestions for improving performance on this table?

    Many thanks in advance for any responses.

  • January 21, 2009 at 1:56 pm

    #930698

    Why are you reindexing it daily? Are you seeing framentation in this table? You should reindex it only if fragmentation is high(>30-40) otherwise do it weekly or at a gap of 3-4 days.

    MJ

  • January 21, 2009 at 2:03 pm

    #930704

    I am not reindexing it daily, currently around every 2-3 weeks.

    Thanks

  • January 21, 2009 at 3:37 pm

    #930759

    What is slow? Select statament queries with Where clause Or Inserts/Updates/Deletes? What is the index structure? Are there too many non-clustered indexes on this table(out of which some not getting used at all too)?

    MJ

  • January 21, 2009 at 8:01 pm

    #930858

    What is the clustered index or key based on? Does it maintain the order that something was inserted or does it try to maintain some other order that would cause page splits?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply