Delete takes extremly long time.

guys, im running MSSQL 2000 latest SP on PIII-1Ghz, 4GB RAM, SCSII and all good things but...

...when i execute this simple query to delete 8,000 records out of 18,000 total in the table it takes 11 seconds:

delete from tempViewResults where opportunity_id = '016158'

i do have an index on opportunity_id.
any ideas why? is this normal?

any help is greatly appreciated.please anybody?|||In this case, the index is not really helping you so much as hurting you. In order for the optimizer to consider using the index, you have to be going after <10% of the table. How many other indexes are there on the table? Also check for triggers on the table that may be firing for deletes.|||Also consider to create that "temp" table in the tempdb, which is faster.|||Two more cents:

Cent #1:Your delete statement will take longer if their are cascading relationships set up with subtables.

Cent #2:I'm not sure if using tempdb would be faster, because it still would be writing data to disk, but if you can get away with using a table variable which uses memory you might be able to avoid disk writes altogether.

blindman|||Using a #temp table is faster if your database is in FULL or BULK-LOGGED model.

Of course if your database is in SIMLE model you won't get any speed improvement|||Manowar:

What makes the temptable faster than a stored table? I couldn't find anything in Books Online regarding this. I'd have thought that querying a stored table would enable the optimizer to take advantage of cached query plans. Could you point me to where this is explained or documented in Books Online or Microsoft's support site?

blindman|||I think there's nothing about it on BOL or MSDN. I've found a note on Kalen Delaney "Inside SQL Server 2000" world-famous book.

You can look in the section about temp (#) tables. Kalen says that since in the tempdb sql server only register log data for rollback purposes (and not for database recovery), data modification operations can be up to for time faster that in a standard db.

Delete records by latest date

Hello,
Can anyone tell me how to delete records from a table
based on dates. For example I have a table of property
sales data. Each property could sell multiple times over
several years. So, the ID # of the property would be the
same each time it sold, but the sale date would be
different. I only want to keep one sale record for each
property ID # and I want it to be the latest sale date.
Here is a sample:
PIN SALEDATE TYPE
6842-564-299 2/12/01 A
6842-564-299 4/17/03 A
6724-312-765 9/5/02 B
6993-411-772 7/21/03 A
The first 2 records above have a duplicate PIN. So I want
to delete the one with the earliest date and keep the
other. Can anyone tell me how to do this in SQL Server
2000. Any help would be greatly appreciatedSo many choices :)
Assuming your table is called YourTable: Also assuming the sales date is a
string (if not you can remove the convert function.
delete YourTable
from YourTable
join
(select PIN, max(SALESDATE) as SALESDATE from YourTable group by PIN having
count(*) > 1) foo
on YourTable.PIN = foo.PIN and convert(smalldatetime,YourTable.SALESDATE) <
convert(smalldatetime,foo.SALESDATE)
Mark
"Shane Sprouse" <shane@.co.greenwood.sc.us> wrote in message
news:0b5301c3aa2f$ddad68e0$a001280a@.phx.gbl...
> Hello,
> Can anyone tell me how to delete records from a table
> based on dates. For example I have a table of property
> sales data. Each property could sell multiple times over
> several years. So, the ID # of the property would be the
> same each time it sold, but the sale date would be
> different. I only want to keep one sale record for each
> property ID # and I want it to be the latest sale date.
> Here is a sample:
> PIN SALEDATE TYPE
> 6842-564-299 2/12/01 A
> 6842-564-299 4/17/03 A
> 6724-312-765 9/5/02 B
> 6993-411-772 7/21/03 A
>
> The first 2 records above have a duplicate PIN. So I want
> to delete the one with the earliest date and keep the
> other. Can anyone tell me how to do this in SQL Server
> 2000. Any help would be greatly appreciated
>