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 TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.