By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Scheduled Job Hangs the server

P: n/a
aaa
I wonder if anyone can help.

I have a scheduled job running overnight to delete old records for a
particular Db table.

The table contains more than half million records and the script
simply uses the date field to delete any of the records which have a
date older than 7 days. My guess is that there will be some 100,000
records which need to be deleted.

The job takes ages to run and whilst it is running sql server is
completely locked and the associated website is effectively offline
because of it.

I think I know where the problem lies but I don't know why.

Whoever created the Db table created a varchar 255 field as the
primary key. The field is then filled with a string of 40 characters
generated within the webserver script to have a 'unique' string.

Those are the facts. The following is my interpretation.

I think the server is unable to cope with not only a character field
as primary key but also one with so many characters in it. In such a
larger table presumably in order to delete the old records it must do
some sort of ordered sort on the Primary Key and it is this that is
causing the problem.

I would like to introduce a new field called 'id' make it autonumbered
and primary and make the errant field non-primary.

So my question is this:

Is my analysis correct but more importantly, why? Can anyone give me
clear reasoning for it.

Also is the solution sound?
Table looks like this:

clientID int
refID varchar 255 Primary Key
fieldA varchar 512
creationDate datetime
fieldB varchar 255
field C varchar 32

Job script:

delete from myTable where creationDate < [7daysAgo]

Thanks in anticipation

Bill
Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Stu
Although your primary key is dubious, the datatype and contents are not
the issue. What are your indexes like? If you do not have an index on
the datefield, and your clustered index is on the primary key that
you've identified, then your delete statement is battling some serioud
fragmentation issues.

Stu

Nov 23 '05 #2

P: n/a
Thanks Stu I'm sure that's it. I'll investigate tomorrow.

Appreciate your help.

Bill

On 20 Nov 2005 06:05:50 -0800, "Stu" <st**************@gmail.com>
wrote:
Although your primary key is dubious, the datatype and contents are not
the issue. What are your indexes like? If you do not have an index on
the datefield, and your clustered index is on the primary key that
you've identified, then your delete statement is battling some serioud
fragmentation issues.

Stu


Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.