Geek Answers Handbook

SQL Server DELETE is slower with indexes

I have a SQL Server 2005 database and I tried to put indexes in the appropriate fields to speed up the DELETErecords from the table with millions of rows (it big_tablehas only 3 columns), but now the execution time is DELETEeven longer! (For example, 1 hour vs 13 minutes)

I have a relationship between tables, and the column that I am filtering DELETEis in another table. for instance

DELETE FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)

By the way, I also tried:

DELETE FROM big_table
WHERE EXISTS
(SELECT 1 FROM small_table
WHERE small_table.id_product = big_table.id_product
AND small_table.id_category = 1)

and although it works a little faster than the first, it is still much slower with indexes than without.

I created indexes in these fields:

  • big_table.id_product
  • small_table.id_product
  • small_table.id_category

My .ldf file grows a lot during DELETE.

DELETE , ? , .

UPDATE

, , , , DELETE, . , , DELETE .

, DELETE, WHERE.

Odetocode.com :

" DELETE UPDATE, SELECT."

, .

bobs:

  • 55
  • 42 .
  • SELECT ( " System.OutOfMemoryException" )

2 :

SELECT * FROM big_table
WHERE big_table.id_product IN (
SELECT small_table.id_product FROM small_table
WHERE small_table.id_category = 1)

SELECT * FROM big_table
INNER JOIN small_table
ON small_table.id_product = big_table.id_product
WHERE small_table.id_category = 1

25 SQL Server 2005:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

- Xeon 7,5 . :), .

- , CREATE , ?

+5
5

- .

, (, DELETE), , . . , , , .

+27

- , , . , .

; , "" ; , -, .

; , , ; , . ; ( , , ), , , indexdefrag ; - , !

+1

TSQL DELETE , :

DELETE FROM big_table
FROM big_table AS b
INNER JOIN small_table AS s ON (s.id_product = b.id_product)
WHERE s.id_category  =1
0

JohnB 75% . , , , . , . . , .

, , .

0

- , ( )

declare @continue bit = 1

-- delete all ids not between starting and ending ids
while @continue = 1
begin

    set @continue = 0

    delete top (10000) u
    from    <tablename> u WITH (READPAST)
    where   <condition>

    if @@ROWCOUNT > 0
        set @continue = 1 

end
0

More articles:


All Articles