For the first execution, the data is not cached in the memory and needs to be read from the hard disk, so it will be very slow. In the second execution, the data has already entered the memory, so it does not need to be read from the hard disk and directly read from the memory.
How does SQL Delete N records published by a Member from millions of records-SQL Retrieval Efficiency
Delete from table200 where mid = xxxxxxx timeout
If select * from table200 where mid = xxxxxxx is used, it also times out.
However, if you use select top 100 * from table200 where mid = xxxxxxx, it will be retrieved soon.
// Solution 1
When data is deleted, other indexes must be updated.
You can use the following statement to manually set query Timeout:
Sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
Sp_configure 'query wait', 2147483647
GO
RECONFIGURE
GO
// Solution 2
Since select top 100 * from table200 where mid = xxxxxxx is fast, try it.
While 1 = 1
Begin
Set rowcount 100;
Delete from table200 where mid = xxxxxxx;
If @ rowcount = 0 break;
End
// Method 3
Select top 100 * from table200 where mid = xxxxxxx 0. x ~ 10
// Method 4
Select * from table200 where mid = xxxxxxx after the first query is completed in 50 seconds, the query is executed in 30 seconds.
For the first execution, the data is not cached in the memory and needs to be read from the hard disk, so it will be very slow.
In the second execution, the data has already entered the memory, so it does not need to be read from the hard disk and directly read from the memory.
?>