Deleting Change Log Entries

DWOpdahl

I have a client whose change log has basically gotten out of control. There are currently 300,000 K entries. The client is running 4.0 SP1 on C/Side. We are trying to delete some of the older entries as this is, obviously, resulting in performance issues. The problem is that with so many entries the report to delete change log entries seems to take so long that it runs into their nightly processing (nightly imports from an offline system).

I noticed that there are two keys on the table: a) Entry No. and b) Table No., Date/Time. I took a copy of the database and disabled the second key. This enabled me to delete entries faster, but only when I knew the Entry No. range that I wanted to delete. I would do this in the live database, but am not sure that disabling that second key will not result in further issues.

Any suggestions as to how to tackle this? Many thanks.

kapamarou

From what I see this report just deletes records. So if you specify a filter on the date that causes less records to be deleted then the commit should be faster.

If you are trying to delete entries for a whole year, let' say, then try to delete entries for the first month, then the second etc...

David_Singleton

DWOpdahl wrote:

I have a client whose change log has basically gotten out of control. There are currently 300,000 K entries. ...

:shock: hmm 300,000 k = 300,000,000 That seriously is a lot of entries. That would be about 60-70Gig in one table. Last week I was working on a database with 73 million log entries, less than a 1/4 of what you have there, and that was difficult, so I can imagine how big a task that was for you.

In your case I would add a counter and every 10,000 or so records do a commit when deleting. The table is not consistent and is not linked anywhere, so losing a few wrong ones would not be an issue.

kapamarou

David Singleton wrote:

Last week I was working on a database with 73 million log entries, less than a 1/4 of what you have there, and that was difficult, so I can imagine how big a task that was for you.

I thought my 9,5 million records was a lot :!:

DWOpdahl

Let me correct myself. The second key on the table is Table No. and Primary Key Field 1 Value -- not 'Date and Time'.

In addition, if I use the report to delete the entries, it begins counting records using both keys running through the first and then the second. If there is some way that I could avoid this, I would appreciate the input.

Thanks.

kapamarou

This is probably because you are filtering using Date while there is no key that supports that.

Actually here it would help you if you had a key with the date.

But this "record counting" appears while the system applies the filter. Once it has applied them it shouldn't take too long to delete them, if you specify smaller date intervals...


By the way, how many records do you have?

Alex_Chow

Instead of filtering by date, why not just use Entry No. to filter?

It should be similar to the posting date since entries are entered in chronological order.

David_Singleton

kapamarou wrote:

By the way, how many records do you have?

He already said that, 300,000,000 which is huge.

kapamarou

David Singleton wrote:

He already said that, 300,000,000 which is huge.

Just wanted to make sure DWOpdahl with

DWOpdahl wrote:

300,000 K

really means 300,000,000 and it's not a typo meaning actually 300,000.
It would be really weird to have 300000 entries taking too long to delete but I was just curious to clear that out.

David_Singleton

kapamarou wrote:

It would be really weird to have 300000 entries taking too long to delete but I was just curious to clear that out.

Exactly which is why I assumed that he meant what he posted. If he has 300k entries and its slow then there is a serious issue.