sql server in performance the problem VARCHAR AND RANDOM VAL


site :https://sqlserverqueryperformance.wordpress.com/2016/04/28/sql-server-in-performance-the-problem/
by NEERAJ PRASAD SHARMA


-- ID Varchar


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 = 9
Set @LoopCount= 1

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 IDVARCHAR  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)
'


EXEC( @dynamicsql)
set @loop= @loop+12
SET @LoopCount =@LoopCount+1
SET @dynamicsql =''
END


select * from #counter
order by variablecount


--- Random Integer

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

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 Randomvalue 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)
'


EXEC( @dynamicsql)
set @loop= @loop+10
SET @LoopCount =@LoopCount+1
SET @dynamicsql =''
END