SQL Server Performance Forum – Threads Archive
Speed Issues with Delete Query
i have this stored procedure that deletes details of a person.It takes ages to complete.I cannot disable triggers/Constraints as this will affect other users.Any help on how to speed it up.
You could delete in smaller batches. But if you need to delete large amounts of data, probably you can schedule this for off-peak hours where none or only few users are in the database. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
There is no generic solution to speed up normal data manipulation actions. On the other hand, if we could have a look at the stored procedure … How often does the sproc get executed?
Does it always take this long? Also we need to know about the indexes on the table from which you’re deleting.
Hai Am also a starting member.I know i may be also going to ask some qustions like this n the coming day.. In this case please include the table constraints(including Priimary key if u have one.)and also ur proc.. to get a clear idea..if ur deleting all the data from a temp table if not need to be logged then use truncate table instead of deleted
Good point, <s>Mirko</s> <b>Adriaan</b>! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Please show us the code, so that we’re able to help you better. <br /><br />Btw sqlkk, there are some limitations on TRUNCATE present, which might make it impossible to use here:<a target="_blank" href=http://www.sql-server-performance.com/q&a118.asp>http://www.sql-server-performance.com/q&a118.asp</a><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
Frank, have you mixed threads or you gave me the credit for Adriaan’s post? [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
Bummer! I seem to have mixed things up! [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Yes, I meant Adriaan’s post. Good point! [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
Below is a sample code.It Repeats same code in several tables.Frank what do u mean by delete in smaller batches?.
—————————-
delete from table_1 where id= @id
set @@error_value = @@error
if ( @@error_value <> 0 )
begin
rollback tran
return 1
end
Something like this
SET ROWCOUNT 5000
DELETE FROM table WHERE condition
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Frank, you should add a nice WHILE loop to that.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />BEGIN TRANSACTION<br /><br />SET ROWCOUNT 5000<br />WHILE EXISTS (SELECT id FROM Table_1 WHERE id = @id)<br />BEGIN<br />DELETE FROM Table_1 WHERE id = @id<br />SET @@error_value = @@error<br />if ( @@error_value <> 0 )<br />begin<br />BREAK<br />end <br />END<br /><br />SET ROWCOUNT 0<br /><br />if ( @@error_value <> 0 )<br />begin<br />rollback tran<br />return 1<br />end<br />else<br />begin<br />COMMIT TRANSACTION<br />return 0<br />end
Aargh, that’s not my day today. [xx(] —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
]]>