Transaction Times on Very Large Table

Question

Transaction Times on Very Large Table

McSQL

SSCarpal Tunnel

Points: 4936
More actions
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.

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

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

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

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 2

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

Thanks

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

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)