DELETE TOP(500) is taking more time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DELETE TOP(500) is taking more time

Author

Topic

Jampandu
Starting Member

12 Posts

Posted - 2014-03-13 : 12:19:28

Hi experts,

below simple delete statement is taking around 50 sec to execute.


DELETE TOP(500) a
FROM Table1 a
WHERE exists (SELECT 1 FROM #table2 b WHERE a.pkcol=b.pkcol)

Here
There is no indexes on Table1 except primary key.
table1 is highly transnational table
#table2 is temp table with 5000 records

Any suggestions please

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-13 : 13:26:06

Is pkcol indexes in #table2? Is the query being blocked at all? Have you tried setting the batch size smaller and deleting again and again until you reach the number?

What does the execution plan show? Add this to your script and show us the output:
set statistics io on
set statistics time on

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources