Delete statement taking more time

Question

Delete statement taking more time

ningaraju.n

SSCrazy

Points: 2322
More actions
August 13, 2010 at 5:22 am

#227736

Hi All,
I am executing delete statement in production server and its taking more time . can any one please explain the reason
my delete statement looks like
: Delete from QuestionAnswerMapping WHERE Assessment Id=@Assessment

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ningaraju.n (8/13/2010)
Hi All,
I am executing delete statement in production server and its taking more time . can any one please explain the reason
my delete statement looks like
: Delete from QuestionAnswerMapping WHERE Assessment Id=@Assessment

Is there an index on [Assessment Id]?

How many rows in the table [QuestionAnswerMapping]?

You say your "delete statement looks like" - is this your delete statement or is there more to it? If so, please post the entire delete statement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ningaraju.n SSCrazy Points: 2322 More actions · Answer 2

Hi Chris,

There are 7K records and has index on column AssessmentId and this is an single statement

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

it should work faster if it doesnt contain any heavy columns ( BLOB type). use can take "BATCH approach"

or looping.

refer this http://www.sqlservercentral.com/articles/Top/63301/

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

ningaraju.n (8/13/2010)
Hi Chris,
There are 7K records and has index on column AssessmentId and this is an single statement

Do you run the same statement many times with different @assessmentID?

How long does one DELETE statement take to run?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) trigger

2) blocking

3) unindex foreign keys

4) horrible tlog write performance

5) server being crushed by other activity

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service