SQL SELECT vs DELETE

Hi Experts,

I have a few queries which take suspiciously long to execute, and I'm wondering if these timings are correct or worrisome?  I'm using SSMS SQL 2008 R2

SELECT * FROM tblReservations;
Had ~70K Records (can't recall exactly, executed in 0s

SELECT * FROM tblReservations WHERE fldTripType IS NULL;
Had ~10K Records (can't recall exactly), executed in 0s

DELETE FROM tblReservations WHERE fldTripType IS NULL;
Same criteria as llast, after 5 min I went to lunch... came back, see executed in 53min! Why?

SELECT * FROM tblReservations WHERE fldAgent IS NULL;
ONLY 134 Records (can't recall exactly), executed in 0s

DELETE FROM tblReservations WHERE fldAgent IS NULL;
Same criteria as last, after 5 min I stopped execution

So, my question is, are these SELECT vs DELETE execution times normal, due to the fact i had >70K Records, or are they alarming?  >5min to delete 134 records?

Thank you.
APD TorontoAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters...
Are there any triggers on that table that fire for a delete action?
Are there any foreign keys on that table that have CASCADE DELETE?
Are there any other users in the database/tables when the delete was executed, where there may be locking/blocking going on?
Does this table have a 'soft delete' column such as IsDeleted (bit), where a better idea would be to set that to whatever value is 'deleted', instead of actually deleting the rows?
0
 
plusone3055Commented:
there could be a lot of factors here

my first thought would be memory on that machine.
unless your going to tell me that these records have hundreds upon hundreds of columns in each record

Another is did you happen to run these queries when there was heavy traffic on that server IE a Massive stored procedure or operation was going on at the time
??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.