February 19, 2013 at 4:36 am
Hello!
When I looping a variable from a table with nolock hint in the while, then the execution time is bigger.
Why?
Here is the script:
use tempdb
go
set nocount on;
set xact_abort on;
go
if OBJECT_ID('tempdb..tmp_zs_456') is not null drop table tmp_zs_456
go
create table tmp_zs_456 (id int primary key identity(1,1), szam int default 0)
go
declare
@id int = 0,
@from_id int,
@to_id int = 10000,
@end_time datetime,
@start_time datetime
while @id < 1000000 begin
set @id += 1
if @@TRANCOUNT = 0 begin tran
insert into tmp_zs_456 default values
if @id % 5000 = 0 AND @@TRANCOUNT <> 0 commit
end
if @@TRANCOUNT <> 0 commit
-- Loop 1: the execution time is ok.
select @start_time = GETDATE(), @from_id = 0
while @from_id < @to_id begin
select top 1 @from_id += 1 from tmp_zs_456
--select top 1 @from_id += 1 from sys.databases
end
set @end_time = GETDATE()
select DATEDIFF(millisecond, @start_time, @end_time) as execution_time_without_nolock
-- Loop 2: the execution time is bigger. Why?
select @start_time = GETDATE(), @from_id = 0
while @from_id < @to_id begin
select top 1 @from_id += 1 from tmp_zs_456 with (nolock) -- Here is the execution time is bigger.
--select top 1 @from_id += 1 from sys.databases with (nolock) -- This is ok.
end
set @end_time = GETDATE()
select DATEDIFF(millisecond, @start_time, @end_time) as execution_time_with_nolock
Thanks
February 19, 2013 at 4:50 am
Because nolock is not "go faster stripes" ? More seriously, probably because the nolock hint is performing a scan which needs to read more pages due to reading in allocation order.
Check out these links for more reasons not to use nolock:
Allocation order scans with nolock
Consistency issues with nolock
Transient Corruption Errors in SQL Server error log caused by nolock
Dirty reads, read errors, reading rows twice and missing rows with nolock
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply