SQL SERVER IN PERFORMACE : The problem

SQL SERVER IN PERFORMACE : The problem

SQL SERVER IN PERFORMACE : The problem

Starting a series of a topic on IN litral values,
i am sure whose ever uses sql does uses “IN” as alternative of muliple “OR” a lot
this a first post i call it the problem where i will leave a question and
continue next blog posts with answer.

as far as i know this topic has`t been toched by anyone don`t know the reason
so better get started ūüôā

 

CREATE TABLE TestTable
(
ID int IDENTITY(1,1) not null ,
IDvarchar as Cast(ID as Varchar(50)) PERSISTED not null,
intcolumn int ,
Name varchar(50) NOT NULL,
Age int NOT NULL,
Randomvalue bigint
)

INSERT into TestTable (intcolumn , Name , AGE)
SELECT s1.number , ‘NEERAJ’, s1.number % 10 + 25
FROM master.dbo.spt_values s1
CROSS JOIN master.dbo.spt_values s2

Update TestTable set Randomvalue = Cast(rand(checksum(newid()))*ID as int)

Alter table TestTable alter column Randomvalue int not null

update statistics TestTable with fullscan

 

befor get started some point for considerators are:

1 table is heap
2 no usefull index on it
3 lookup column consist unique integer values
4 litral values are existing in table and random and with unique values
5 QO is limmited to use 1 core (parallism desabled )

to masure the performace diffrence i created a script
that run against the provided litral values with loop and calculate total time
taken in exiecutng those values and put result in temporary table
column result are assign to local variable just to restrict sending data to grid

 

For example :
–1st query will execute as

Select @ID ,IDvarchar ,intcolumn, Name ,Age ,Randomvalue from TestTable
where id in
(
3279615
)

–2nd query will execute as

Select @ID ,IDvarchar ,intcolumn, Name ,Age ,Randomvalue from TestTable
where id in
(
3279615, 2619534\
)

–3d query will execute as

Select @ID ,IDvarchar ,intcolumn, Name ,Age ,Randomvalue from TestTable
where id in
(
3279615, 2619534, 2932146
)
and so on …. upto 99 values

SCRIPT BELOW

 

Create table #counter (variablecount int primary key , milliseconds varchar(10))

declare @values nvarchar (max)
select @values= (‘3279615, 2619534, 2932146, 4953386, 2818224, 5244141, 2321234, 4670287, 3600354, 4307158, 4668756, 4049137, 3037542, 4483522, 3660501, 4426646, 3110987, 2709820, 2818736, 4875826, 2029820, 5467223, 2316672, 2080036, 3781872, 5120161, 4231500, 2885329, 2186636, 4273746, 4469182, 4682959, 3905551, 3206172, 2911743, 4275209, 3789552, 2341547, 2943986, 5229815, 2867115, 2402580, 2943328, 3125357, 4816147, 4219152, 3825625, 3221610, 5052178, 4742174, 2182772, 4934910, 5196299, 3803372, 5041037, 4261499, 5266382, 3667876, 3467411, 2574704, 3941358, 3785855, 4258958, 4565076, 5274231, 3203938, 5329843, 4438383, 2312628, 3248408, 3743514, 4552594, 4109833, 4388393, 5064054, 2054373, 3715411, 3761234, 5470921, 5197951, 4356672, 3309015, 3767928, 2372922, 3858804, 3617027, 3247204, 2105261, 2846260, 3053944, 4398447, 3246406, 4326008, 2506261, 5404760, 2410146, 3578365, 2753024, 3164078, 4087679’)
Declare @dynamicsql nvarchar(max)
declare @loop int ,@LoopCount int
set @loop = 7
Set @LoopCount= 1
–648
while (@loop <997)
BEGIN
SET @dynamicsql =
‘declare @Beforeexecutiontime datetime
declare @Aeforeexecutiontime datetime
declare @ID int
declare @IDvarchar varchar(50)
declare @INTCLOUMN int
declare @Name varchar(50)
declare @Age int
declare @Randomvalue bigint

Set @Beforeexecutiontime=getdate()

Select @ID=ID , @IDVARCHAR=IDvarchar ,@INTCLOUMN=intcolumn, @NAME=Name ,@AGE=Age ,@RANDOMVALUE=Randomvalue from TestTable
where id in
(‘ +Convert (Nvarchar(max), left ( @values,@loop))+ ‘)
option (MAXDOP 1)

SET @Aeforeexecutiontime =getdate()
INSERT INTO #counter
select ‘+ Convert (Nvarchar(10),@LoopCount)+’,datediff (MS ,@Beforeexecutiontime ,@Aeforeexecutiontime)

–select @dynamicsql
EXEC( @dynamicsql)
set @loop= @loop+10
SET @LoopCount =@LoopCount+1
SET @dynamicsql =”
END

Results in  Microsoft SQL Server 2014 Р12.0.2000.8 (X64) 
2014Results in Microsoft SQL Server 2012 Р11.0.2100.60 (Intel X86) Express Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) Capture

Result in Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Enterprise Edition2008

Result in Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86)
2005

Notice unusual when it reach 16 and then bizzar when reach 66 and above why?
i `ll leave explanation for next post.

What are the results on your system ?

script here
http://pastebin.com/MbDba2QG

 

 

 

 

 

Advertisements