SQL Server Delete data from linked server via Openquery takes a long time

oogrdqng 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(133)

I have a problem with deleting record on linked server, I tried both of the command below.

DELETE OPENQUERY (SRT, 'SELECT * FROM DP.STENCIL_LABEL');

DELETE FROM OPENQUERY (SRT, 'SELECT * FROM DP.STENCIL_LABEL');

15 minutes passed by it still won't finish so I cancel the execution.

I have around 85k record on that table, meanwhile inserting same amount of data to the same table via OPENQUERY only takes less than 2 minutes.

Any help?

Thank you.

1条答案

Looks like you are retrieving all data first and then deleting it. Why not delete it directly with a DELETE FROM or TRUNCATE TABLE statement?

Also, if your server is linked, you can specify the table from the linked server, no need for openquery() .

Try this:

truncate table SRT.YourDatabase.DP.STENCIL_LABEL;