Spread the love

IN Vs INNER JOIN

Here we will do a quick benchmark on using IN vs INNER JOIN.

This came about when a coworker had to run a one-off SQL statement to see if approximately 3,500 codes (from a supplied text file) were in a massive table.

She asked a few colleagues on a best approach and there were two main schools of thought:

the easiest way was to just paste them into one long WHERE…IN statement
load them into a table and perform an INNER JOIN

That’s where this Curious Consultant started wondering what the fastest way would be: IN vs INNER JOIN. Hence this fresh reading material. 🙂

The Caveats

Before everyone leaves comments on how this isn’t an entirely accurate test, this is why the code was used below:

We don’t care about INNER JOINs returning duplicates; we just want to know if a record with the code is in the table
we don’t care about execution plans of each and their differences; we only care about about how long these queries take start to finish.
we don’t care that the INNER JOIN can return other columns

The Results Are In!

Here are the results, in milliseconds, as to how long each technique took to execute.

The winner is highlighted in green.

Technique	Time to execute, in milliseconds, over 3 runs:
# Records:	360	720
1: IN statement	17, 16, 16	46, 34, 30
2: Non-Indexed column	0, 2, 0	0, 13, 0
3: Clustered Indexed column	0, 0, 0	0, 0, 0
3: Non-clustered Indexed column	0, 0, 0	0, 0, 0

The Way To Go

There doesn’t appear to be any surprises in the results when more than just a few records are used.

When less than 100 records are used (results not displayed), everything ran in zero milliseconds.

When nearing the max size of the varchar content allowed, the time to run isn’t that significant to be noticed by the average user. Sure, if it’s in some sort of automated system that you need the fastest way to go, by all means alter the SQL.

Otherwise, do whatever works best for you. Or if your inner-geek is wanting to come out too, arrange your own test to play around. Be sure to post your results here in the comments below. 🙂

The SQL Source Code Used

checkpoint
-- Clear all the caches so this test can run without biased results ----
checkpoint
go
DBCC DROPCLEANBUFFERS
go
DBCC FREESESSIONCACHE
go
DBCC FREEPROCCACHE
go
DBCC FREESYSTEMCACHE('ALL')
go

--The unique string to look for in the queries. 
DECLARE @WhatToLookFor varchar(12) = '$%XX#abcde'

--The meximum number of strings to generate. 
--If you change this value, you also need to update the strings in the "IN" clause
--further below in the code! 720 is the max this can be for the varchar(MAX) variable 
DECLARE @MAX int = 720

--===== NOTHING BELOW THIS LINE NEEDS TO BE CONFIGURED EXCEPT		==============
--===== THE LINE WITH THE "IN" CLAUSE INDICATED IN THE CODE BELOW.	==============

DECLARE @Counter int = 1
DECLARE @tempString varchar(MAX) = ''
DECLARE @tempGeneratedString varchar(50)
DECLARE @Start_Time datetime
DECLARE @End_Time datetime
DECLARE @TestUsingInClause varchar(50) = 'Test using IN statement'
DECLARE @TestAgainstNonIndexedColumn varchar(50) = 'Test against non-indexed column'
DECLARE @TestAgainstClusteredIndexedColumn varchar(50) = 'Test against clustered indexed column'
DECLARE @TestAgainstNonClusteredIndexedColumn varchar(50) = 'Test against non-clustered indexed column'

--create temp table to hold all generated data
IF OBJECT_ID('tempdb..#tableWithGeneratedData')IS NOT NULL
	DROP TABLE #tableWithGeneratedData
Create Table #tableWithGeneratedData
(
	id int identity(1,1),
	aClusteredIndexedColumn varchar(50) not null,
	aNonClusteredIndexedColumn varchar(50) not null,
	aNonIndexedColumn varchar(50) not null
)

--create temp table to query against
IF OBJECT_ID('tempdb..#tableWithStringToQueryFor')IS NOT NULL
	DROP TABLE #tableWithStringToQueryFor
Create Table #tableWithStringToQueryFor
(
	id int not null,
	theString varchar(50) not null
)

--create temp table to hold test results
IF OBJECT_ID('tempdb..#tableToHoldQueryResults') IS NOT NULL
	DROP TABLE #tableToHoldQueryResults
Create Table #tableToHoldQueryResults
(
	id int not null,
	matchedData varchar(50) not null
)

--create temp table to hold resulting stats
IF OBJECT_ID('tempdb..#stats') IS NOT NULL
	DROP TABLE #stats
Create Table #stats
(
	id int identity(1,1),
	[task] varchar(900) not null,
	TotalRecordsCompared int null,
	MatchingRecords int null,
	StartTime datetime null,
	EndTime datetime null,
	TimeToRun varchar(50) not null
)

print CAST(GETDATE() as varchar) + ' Started while loop creating temptable data.'
SET NOCOUNT ON

While @Counter <= @MAX
BEGIN
	--To ensure one match in the middle of everything
	If (@Counter = (@MAX / 2))
	BEGIN
		SET @tempString = @tempString + '''' + @WhatToLookFor + ''','
		INSERT INTO #tableWithStringToQueryFor (id, theString)
			VALUES (@Counter, @WhatToLookFor)
	END
	ELSE
	BEGIN
		SET @tempGeneratedString = LEFT(CAST(NEWID() as varchar(50)),8)
		SET @tempString = @tempString + '''' + @tempGeneratedString + ''''
		If (@Counter < @Max)
			SET @tempString = @tempString + ','
	END

	SET @Counter = @Counter + 1
END

print LEN(@tempString)
--To get the string values to place in the "IN" statement
print ('Copy/Paste the next line with the generated values to place within the "IN" clause in the code.')
print @tempString
SET NOCOUNT OFF

--Put all the generated strings into a #temp table to query using a join
DECLARE @XML XML
SET @XML = CAST(
	('<i>' + REPLACE(REPLACE(@tempString,'''',''), ',', '</i><i>') + '</i>')
	AS XML)
INSERT INTO #tableWithGeneratedData (aClusteredIndexedColumn, aNonClusteredIndexedColumn, aNonIndexedColumn)
SELECT
	t.i.value('.', 'VARCHAR(MAX)') 
	,t.i.value('.', 'VARCHAR(MAX)') 
	,t.i.value('.', 'VARCHAR(MAX)') 
FROM @XML.nodes('i') AS t(i)
WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''

print CAST(GETDATE() as varchar) + ' Finished creating temptable data.'

--create the indexes
print CAST(GETDATE() as varchar) + ' Started creating temptable indexes.'
CREATE CLUSTERED INDEX IDX_cl ON #tableWithGeneratedData(aClusteredIndexedColumn)
CREATE NONCLUSTERED INDEX IDX_noncl ON #tableWithGeneratedData(aNonClusteredIndexedColumn)
CREATE NONCLUSTERED INDEX IDX_id ON #tableWithGeneratedData(id)
print CAST(GETDATE() as varchar) + ' Finished creating temptable indexes.'

--verify creation
--select top 10 * from #tableWithGeneratedData order by id

--run the tests!
         
--using the "IN" statement first -------------------------------------------
print 'Starting ' + @TestUsingInClause
SET @START_TIME = GETDATE()

SELECT @TestUsingInClause, * 
FROM #tableWithStringToQueryFor ttq
WHERE ttq.theString IN
--=================================================================================
--
--BELOW IS WHERE YOU PLACE YOUR LONG LIST OF STRINGS TO TEST THE 'IN' FUNCTIONALITY.
--
--=================================================================================
('ED4A8CAE','9B134549','29D2FF54','03C1D80A','D88897C6','DF8FED17','61614C5D','BE4D8BC3','5B651D1C','6A1DACC4','96CE11CB','1BC52900','085D65FB','92241A88','FAAA9F9F','0EAD4FC1','31621330','BE27C0A8','AD369DE5','8CBA2946','98B72EBC','55F19395','D3FB77C4','660DCFA3','9C347A09','97C032F7','7AD77598','8FA31DCF','3639F7CF','404C7F77','80B9BDAA','5B41020B','8D1A9163','B84C32A4','01AC044D','58DB56C6','2BE577A1','8E7F4D74','A5298E0E','8C2D607C','F1F2383A','B629B10D','C925BB49','98576F50','43954DB9','CCE30F20','BECCA0B8','A246D623','1663C110','EDC41534','7C2EC85F','C20A607F','44E197C5','A76B5A92','86C10F68','B0792206','EF1FC95F','3A69BDB6','0967299A','6EEFDC8F','322B6059','C1708DB3','029D27AF','BB20A2A4','DABB92FF','C9890F84','576E722F','B6DD52E4','659B8A53','9B10B97C','19E2AA25','55F15D43','3AF7517C','D12E54C2','310EBD98','E9E3CCDB','FC10CD45','1EC8AC7A','C3BDBFB9','20EBB515','515F5DD8','89224251','078AAF80','8F3FFA21','7EFB4BEF','6B11CE79','00869D0F','4F5C7D09','6D351F24','B3985CBC','103175CD','34D0EADF','156FBA2B','698B0AF0','E8DF250E','9BB33EFF','5680DE7D','66AF12EB','B61F0A45','A52CDA27','BC255F97','7F999393','A05F9705','6D7C9576','BB2D21F1','24F3A467','5F0716FA','B2A58DDB','D120F4FD','EE4EE4D1','FB8D808B','75D2D791','E23A803A','FCA41B42','C9928520','B13A95AA','0413938B','4609D2E4','5E95E68C','B129BF26','7823C3DA','1C0FC056','39637CF9','744822A2','62C5DB99','009BAA26','894A5BB0','7C914D8D','8C0FC6E2','57F75DB5','80D119B3','E9D1EEB8','0BAD9470','C5261864','916B811C','E714C0D9','5B2645A5','B9FD124B','D56E81F8','4075FB49','E4E0B11E','7CDFE6AA','66E48822','3C7522DA','4E1D4317','336E5152','5FC380FB','41AF5020','2EA6FF0A','96369896','C51EB7AC','0E046270','6C919803','2C221252','82EFB13A','C4EA3397','AC73B4C7','689CD70F','F1E60764','2D5140B1','97ED79E8','5AF72426','06025B8E','708DD888','9B66E945','3DEB4182','53DE02D9','066A60DC','7365E65C','66D8EBF7','AD72E497','99D92DC5','C15E3B5B','3C171A1F','B1BC332C','BF92E52E','25EA1B4A','3B084592','E16F2C09','BE022780','41B55E80','063EE708','0484041F','FF2BC991','6CCD2B31','680C125B','C581D0A3','55AD0D62','6D0847E6','2ACBDC96','091D4517','867CA719','F7ACFD80','B17460C2','D8F0BF9A','39BCD912','80AC9B8F','A28D8476','0AF7FA0D','FDEEE3AB','EB01014F','0E64A864','070B2A5C','41FC5EA9','E57DE3D4','2E51F9B7','E011B843','BEE06B43','9EBB67D3','CF0DF746','15CF6367','9C36BF4B','211CAE13','AFE54839','5711A76C','417C89E5','EE2601C0','F49EAB8A','1F698C77','BE82E557','A0AE98B0','D1AEA1F0','4BC420B0','4A4AA457','2A28C180','ABD2541D','30DFB0B4','1B534A66','8EFDA394','ECE9089C','FB8FFE3B','F8AABC1C','FF54DCE8','7B3D36AE','EB6E542A','33DB699E','F8AA73DE','D868CFC3','0DFE385A','BFAC61F7','936B20CB','EB216C1D','C379A293','E1411931','18977D31','6BFE1B44','2A349642','B9FFBC0E','57FA67CC','D2737457','7448E2E1','C30784E9','D04B5ECA','014B8BE7','8B5578F7','EB60627D','4E6F2DDA','692A01E5','1D307915','588C54C6','723D971D','FABCAB1F','2E05744F','D0A04158','7042552B','D3771781','49B52858','1B0E7731','2AC5C78F','AB077ADF','CB97F472','A07A11B9','A2A7B73E','A4449DD1','7CA18A4B','107FC715','B94CC1AC','6C0A3639','2443EBF9','9BA19D75','2A728C94','B3FF567E','25AEBFF2','E5C48D10','22C3E437','6A8013E1','4D47341B','CC35E7C6','B11E2DC1','3DA30794','C63F3645','162D71BC','24AF7A3C','3BB09B86','97DEFFE6','E50C3E22','697FF4FB','E4EA94E1','F1765637','1238898C','635DE7ED','201A3937','C04342FE','2D1DFF4B','7E016EDC','8134F92E','04FA0D27','23C30990','B9BBAA40','D1461B30','D9588F82','3384FF98','15DC4CB0','40AFAC6A','BA32B9CB','7F0AF057','8F2D97EA','A8918B5D','0E9A60CC','2E73703D','0E2C8D37','CE46B6AA','A4DCEBC2','FD76B699','07EEAF05','DC0D4A0A','13DE26AB','B9E0A89A','005DA2A7','85037BE1','470D95A5','FD4F6691','301F0B2F','3A1BFB90','61562E99','7800D9B6','C36E83EE','3B2C8C4A','06E91876','D1D4709F','1D53CAE3','5B40BA6F','1D1EFBFF','7E04ACCF','E6FD56CD','84D12C22','448FAAAB','792B58A7','FE132AF0','BF0E2D3C','0C21584F','337DA65B','00D0BE3C','BF199350','56E3EB55','9ABFA924','9BF3018F','63FA05F8','524008A3','$%XX#abcde','AEFB0ADD','485644C2','ADF3F3D1','16767F71','67A60030','DF65A536','8AC4A12F','2603DBD4','757A4647','3A5BC082','61FEF794','DA9F83CF','401E6FED','A12888AC','0E4ECC5C','EC8F059B','B55A1287','C6A8D580','058F2D75','957B6FFF','8797F10A','ADEA6E39','9CDD77BB','1CBF8738','283C1E20','8775280C','C3FFAF23','6D27DCC9','E3D14B94','033606A8','EE7548A6','9910561D','BFBD8C17','0BFDBF8E','D781472A','091ECD13','B3109128','AEA1DA35','E2906045','FBAFC091','FD5D85B8','5E13C841','F192ECE0','B60EFC8A','10D6AC91','168A1B35','A3DFA6BD','1261F98C','8536B115','1628470A','8A057B1A','B8E44E20','941D0063','202016C9','65D1769E','96489634','8C68D788','F0FEFB54','BB8D4185','9DF1D0A2','461657AB','3A327EE1','8308426B','47CABAA0','B87E4209','6F039CA3','CD6230CD','8D83AC1A','DAC59685','939659E7','667B2011','94B36228','8C71E9D4','D5BA10AA','16592165','0C984928','D609EC6D','73DD5BDE','FE99268E','0A11D76E','95155A89','39A0F91F','2B4BD44E','EA07C005','FAD48F86','7A06EB2E','F50A816C','10D6C3D9','C54D2BCD','1B2753E2','8DEC366F','E6C12444','22534120','41BE729A','63552A5E','8FBD7183','153E8629','CE533540','6EE55908','1992FAF9','5F431B5C','A3700152','F47EABC6','1ED84027','349F2EC2','D1E07863','A2ABCC9E','E3A7DE72','0D08FA56','BB58046E','E67E1241','6DC07E65','BFD82AA4','45125E76','11E3D2E8','63C1156F','6C5690F4','A82244FC','2BB0B73A','16FA303F','50A389E0','75A477A7','8F517361','7B13ADB1','42D7FBDA','D35A8E2C','81AC882D','3E0E9789','3F9ADF1C','BD525C16','18C7F070','48BE040C','A9A456A0','C7188F68','371DD874','DE067925','FC6AA78D','C0098A96','6E67BA1B','9ACC5912','9EBA0497','B5DC664F','0E7F9C75','41E25D7D','8AC91F09','0B839C15','59A4E559','941F26AE','36EEF028','465E532E','834E94D4','F86A73F6','96EE1995','6DB2557D','3DD1E3A1','2E8D71AC','A196A822','4DB27C4A','71DCF35F','BDB5C9BF','32DEFD75','9303C764','3A663C82','4E776EDE','70D2B4B6','EF28E5BB','29AAFB54','6B264440','AD23B39E','66A2F628','8691235D','D49B50AF','2B5B959C','AB83304A','9BEC7F30','FF260211','A844B6EA','F4DC8E62','B266DF98','B7731773','5CEC212B','F65A4A51','9A0DFCFE','9597A0D0','7B29B115','61DD9138','8463C6B6','F4907EC5','314D0BA7','4F3CED50','30F5722B','A97C38CD','6D124F5C','32CEFDFC','06722494','21EA1683','28957316','D215FAF9','81D56549','C4E00878','48D67FD8','025F4F96','0DA9A804','0024C4D9','745DA497','72BA8679','332AF6A9','105E93B9','89495BA2','E6E98C02','7877C110','44478E00','C458EDF1','EE69FF97','43DC3D47','A311CC9F','1FA626F2','61E4CA6F','A8F35062','55CB28C6','0611EE0A','472343E8','70B6A181','50C836EE','F2C64915','6C5EDD4B','562CEEDE','48828688','DE36299E','4817F556','D817EFA5','0F6C87B9','081ECF1E','D229F720','8B073CBF','94FCEB6B','34FDA5EE','AE3925FC','87AC811A','F15F65FD','7413D81B','4365147D','EB266FED','25BD5382','51009F1D','112472EA','B8BCC8AB','1C6A92E8','E05374DB','599F0F10','5C9C640F','D18D4403','E4DEE985','5C14D7D2','55170AF9','F9ADDF69','DD025092','BE6C2735','6AADE3C3','13CCB013','E79C09E6','41B4315A','A955A2F9','0A89FB9B','66A7C266','F2C26867','A2080491','65FFB23A','D109FB8D','601C8EEE','74699444','16F5801F','832B4B19','9DD3F79D','2BF62FFE','261BE981','037952C2','18BB961E','94943FC7','35BA11F2','8BF9F513','620ECBE0','A8B76B3E','679B063E','E743757F','0CC5CAAF','72B4EFCE','6FE96434','57ACDAB6','20B13701','552B61C8','EBC88DF6','2AA97838','F0E70BA8','EF3F715A','5F6E1397','CD17AD14','D0502877','FB3F516D','8C302EE4','6AAAD396','BBCBDE53','08547506','B94887D0','ABC559C3','F7F6D248','1FA8017E','962946C2','DDA69020','88367F6B','2085F0B5','2E492611','8B805ADF','8709930C','D10B26AF','BA8E7163','573016A3','B48447EE','DED890E8','24251E73','224E86CE','8F1F8BE5','BED3FEDD','259DFEFF','A5143673','4479EC1C','0FE917C5','F52B7588','CB309E8F','470B4F0D','A867DFD2','D7DE3011','5259B52D','05C7697A','808CF1DE','6DEB39C1','C6C4B870','97F18401','12C6214D','A1B9395B','2D29CAA1','A312DCC2','55E34BCC','3C3DB43B','037871A3','C26C675C','250033FD','E753786A','1AF1A81E','02EFDF94','B917CAC6','5E05BCC0','3384F95C','A9315DF4','D83D9287','7EEF6F5B','C1AAA91D','4CA57BE6','3504B63A','8266B58A')
--=================================================================================

SET @END_TIME = GETDATE()
print 'Finished'

INSERT INTO #tableToHoldQueryResults
SELECT id, aClusteredIndexedColumn
FROM #tableWithGeneratedData t1
WHERE t1.aClusteredIndexedColumn = @WhatToLookFor

INSERT INTO #stats
SELECT @TestUsingInClause
	, @MAX
  	, count(*) as [Matching Records]
	, @START_TIME as [Start Time]
	, @END_TIME as [End Time]
	, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #tableToHoldQueryResults

Truncate Table #tableToHoldQueryResults

--against non-indexed column -------------------------------------------
print 'Starting ' + @TestAgainstNonIndexedColumn
SET @START_TIME = GETDATE()

SELECT @TestAgainstNonIndexedColumn, * FROM #tableWithStringToQueryFor ttq
INNER JOIN #tableWithGeneratedData t1 on ttq.theString = t1.aNonIndexedColumn
WHERE t1.aNonIndexedColumn = @WhatToLookFor

SET @END_TIME = GETDATE()
print 'Finished'

INSERT INTO #tableToHoldQueryResults
SELECT id, aNonIndexedColumn
FROM #tableWithGeneratedData t1
WHERE t1.aNonIndexedColumn = @WhatToLookFor

INSERT INTO #stats
SELECT @TestAgainstNonIndexedColumn
	, @MAX
	, count(*) as [Matching Records]
	, @START_TIME as [Start Time]
	, @END_TIME as [End Time]
	, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #tableToHoldQueryResults

Truncate Table #tableToHoldQueryResults

--against clustered indexed column -------------------------------------------
print 'Starting ' + @TestAgainstClusteredIndexedColumn
SET @START_TIME = GETDATE()

SELECT @TestAgainstClusteredIndexedColumn, * FROM #tableWithStringToQueryFor ttq
INNER JOIN #tableWithGeneratedData t1 on ttq.theString = t1.aClusteredIndexedColumn
WHERE t1.aClusteredIndexedColumn = @WhatToLookFor

SET @END_TIME = GETDATE()
print 'Finished'

INSERT INTO #tableToHoldQueryResults
SELECT id, aClusteredIndexedColumn
FROM #tableWithGeneratedData t1
WHERE t1.aClusteredIndexedColumn = @WhatToLookFor

INSERT INTO #stats
SELECT @TestAgainstClusteredIndexedColumn
	, @MAX
	, count(*) as [Matching Records]
	, @START_TIME as [Start Time]
	, @END_TIME as [End Time]
	, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #tableToHoldQueryResults

Truncate Table #tableToHoldQueryResults

--against nonclustered indexed column ----------------------------------------
print 'Starting ' + @TestAgainstNonClusteredIndexedColumn
SET @START_TIME = GETDATE()

SELECT @TestAgainstNonClusteredIndexedColumn, * FROM #tableWithStringToQueryFor ttq
INNER JOIN #tableWithGeneratedData t1 on ttq.theString = t1.aNonClusteredIndexedColumn
WHERE t1.aNonClusteredIndexedColumn = @WhatToLookFor

SET @END_TIME = GETDATE()
print 'Finished'

INSERT INTO #tableToHoldQueryResults
SELECT id, aNonClusteredIndexedColumn
FROM #tableWithGeneratedData t1
WHERE t1.aNonClusteredIndexedColumn = @WhatToLookFor

INSERT INTO #stats
SELECT @TestAgainstNonClusteredIndexedColumn
	, @MAX
	, count(*) as [Matching Records]
	, @START_TIME as [Start Time]
	, @END_TIME as [End Time]
	, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]
FROM #tableToHoldQueryResults
Truncate table #tableToHoldQueryResults

--Display the results--------------------------------------
select *
from #stats
order by id

--clean up-------------------------------------------------
IF OBJECT_ID('tempdb..#tableWithGeneratedData') IS NOT NULL
	DROP TABLE #tableWithGeneratedData

IF OBJECT_ID('tempdb..#tableToHoldQueryResults') IS NOT NULL
	DROP TABLE #tableToHoldQueryResults

IF OBJECT_ID('tempdb..#tableWithStringToQueryFor') IS NOT NULL
	DROP TABLE #tableWithStringToQueryFor

IF OBJECT_ID('tempdb..#stats') IS NOT NULL
	DROP TABLE #stats

-----------------------------------------------------------

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

checkpoint

-- Clear all the caches so this test can run without biased results ----

checkpoint

DBCC DROPCLEANBUFFERS

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE('ALL')

--The unique string to look for in the queries.

DECLARE @WhatToLookFor varchar(12) = '$%XX#abcde'

--The meximum number of strings to generate.

--If you change this value, you also need to update the strings in the "IN" clause

--further below in the code! 720 is the max this can be for the varchar(MAX) variable

DECLARE @MAX int = 720

--===== NOTHING BELOW THIS LINE NEEDS TO BE CONFIGURED EXCEPT ==============

--===== THE LINE WITH THE "IN" CLAUSE INDICATED IN THE CODE BELOW. ==============

DECLARE @Counter int = 1

DECLARE @tempString varchar(MAX) = ''

DECLARE @tempGeneratedString varchar(50)

DECLARE @Start_Time datetime

DECLARE @End_Time datetime

DECLARE @TestUsingInClause varchar(50) = 'Test using IN statement'

DECLARE @TestAgainstNonIndexedColumn varchar(50) = 'Test against non-indexed column'

DECLARE @TestAgainstClusteredIndexedColumn varchar(50) = 'Test against clustered indexed column'

DECLARE @TestAgainstNonClusteredIndexedColumn varchar(50) = 'Test against non-clustered indexed column'

--create temp table to hold all generated data

IF OBJECT_ID('tempdb..#tableWithGeneratedData')IS NOT NULL

DROP TABLE #tableWithGeneratedData

Create Table #tableWithGeneratedData

(

id int identity(1,1),

aClusteredIndexedColumn varchar(50) not null,

aNonClusteredIndexedColumn varchar(50) not null,

aNonIndexedColumn varchar(50) not null

)

--create temp table to query against

IF OBJECT_ID('tempdb..#tableWithStringToQueryFor')IS NOT NULL

DROP TABLE #tableWithStringToQueryFor

Create Table #tableWithStringToQueryFor

(

id int not null,

theString varchar(50) not null

)

--create temp table to hold test results

IF OBJECT_ID('tempdb..#tableToHoldQueryResults') IS NOT NULL

DROP TABLE #tableToHoldQueryResults

Create Table #tableToHoldQueryResults

(

id int not null,

matchedData varchar(50) not null

)

--create temp table to hold resulting stats

IF OBJECT_ID('tempdb..#stats') IS NOT NULL

DROP TABLE #stats

Create Table #stats

(

id int identity(1,1),

[task] varchar(900) not null,

TotalRecordsCompared int null,

MatchingRecords int null,

StartTime datetime null,

EndTime datetime null,

TimeToRun varchar(50) not null

)

print CAST(GETDATE() as varchar) + ' Started while loop creating temptable data.'

SET NOCOUNT ON

While @Counter <= @MAX

BEGIN

--To ensure one match in the middle of everything

If (@Counter = (@MAX / 2))

BEGIN

SET @tempString = @tempString + '''' + @WhatToLookFor + ''','

INSERT INTO #tableWithStringToQueryFor (id, theString)

VALUES (@Counter, @WhatToLookFor)

END

ELSE

BEGIN

SET @tempGeneratedString = LEFT(CAST(NEWID() as varchar(50)),8)

SET @tempString = @tempString + '''' + @tempGeneratedString + ''''

If (@Counter < @Max)

SET @tempString = @tempString + ','

END

SET @Counter = @Counter + 1

END

print LEN(@tempString)

--To get the string values to place in the "IN" statement

print ('Copy/Paste the next line with the generated values to place within the "IN" clause in the code.')

print @tempString

SET NOCOUNT OFF

--Put all the generated strings into a #temp table to query using a join

DECLARE @XML XML

SET @XML = CAST(

('<i>' + REPLACE(REPLACE(@tempString,'''',''), ',', '</i><i>') + '</i>')

AS XML)

INSERT INTO #tableWithGeneratedData (aClusteredIndexedColumn, aNonClusteredIndexedColumn, aNonIndexedColumn)

SELECT

t.i.value('.', 'VARCHAR(MAX)')

,t.i.value('.', 'VARCHAR(MAX)')

FROM @XML.nodes('i') AS t(i)

WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''

print CAST(GETDATE() as varchar) + ' Finished creating temptable data.'

--create the indexes

print CAST(GETDATE() as varchar) + ' Started creating temptable indexes.'

CREATE CLUSTERED INDEX IDX_cl ON #tableWithGeneratedData(aClusteredIndexedColumn)

CREATE NONCLUSTERED INDEX IDX_noncl ON #tableWithGeneratedData(aNonClusteredIndexedColumn)

CREATE NONCLUSTERED INDEX IDX_id ON #tableWithGeneratedData(id)

print CAST(GETDATE() as varchar) + ' Finished creating temptable indexes.'

--verify creation

--select top 10 * from #tableWithGeneratedData order by id

--run the tests!

--using the "IN" statement first -------------------------------------------

print 'Starting ' + @TestUsingInClause

SET @START_TIME = GETDATE()

SELECT @TestUsingInClause, *

FROM #tableWithStringToQueryFor ttq

WHERE ttq.theString IN

--=================================================================================

--BELOW IS WHERE YOU PLACE YOUR LONG LIST OF STRINGS TO TEST THE 'IN' FUNCTIONALITY.

--=================================================================================

('ED4A8CAE','9B134549','29D2FF54','03C1D80A','D88897C6','DF8FED17','61614C5D','BE4D8BC3','5B651D1C','6A1DACC4','96CE11CB','1BC52900','085D65FB','92241A88','FAAA9F9F','0EAD4FC1','31621330','BE27C0A8','AD369DE5','8CBA2946','98B72EBC','55F19395','D3FB77C4','660DCFA3','9C347A09','97C032F7','7AD77598','8FA31DCF','3639F7CF','404C7F77','80B9BDAA','5B41020B','8D1A9163','B84C32A4','01AC044D','58DB56C6','2BE577A1','8E7F4D74','A5298E0E','8C2D607C','F1F2383A','B629B10D','C925BB49','98576F50','43954DB9','CCE30F20','BECCA0B8','A246D623','1663C110','EDC41534','7C2EC85F','C20A607F','44E197C5','A76B5A92','86C10F68','B0792206','EF1FC95F','3A69BDB6','0967299A','6EEFDC8F','322B6059','C1708DB3','029D27AF','BB20A2A4','DABB92FF','C9890F84','576E722F','B6DD52E4','659B8A53','9B10B97C','19E2AA25','55F15D43','3AF7517C','D12E54C2','310EBD98','E9E3CCDB','FC10CD45','1EC8AC7A','C3BDBFB9','20EBB515','515F5DD8','89224251','078AAF80','8F3FFA21','7EFB4BEF','6B11CE79','00869D0F','4F5C7D09','6D351F24','B3985CBC','103175CD','34D0EADF','156FBA2B','698B0AF0','E8DF250E','9BB33EFF','5680DE7D','66AF12EB','B61F0A45','A52CDA27','BC255F97','7F999393','A05F9705','6D7C9576','BB2D21F1','24F3A467','5F0716FA','B2A58DDB','D120F4FD','EE4EE4D1','FB8D808B','75D2D791','E23A803A','FCA41B42','C9928520','B13A95AA','0413938B','4609D2E4','5E95E68C','B129BF26','7823C3DA','1C0FC056','39637CF9','744822A2','62C5DB99','009BAA26','894A5BB0','7C914D8D','8C0FC6E2','57F75DB5','80D119B3','E9D1EEB8','0BAD9470','C5261864','916B811C','E714C0D9','5B2645A5','B9FD124B','D56E81F8','4075FB49','E4E0B11E','7CDFE6AA','66E48822','3C7522DA','4E1D4317','336E5152','5FC380FB','41AF5020','2EA6FF0A','96369896','C51EB7AC','0E046270','6C919803','2C221252','82EFB13A','C4EA3397','AC73B4C7','689CD70F','F1E60764','2D5140B1','97ED79E8','5AF72426','06025B8E','708DD888','9B66E945','3DEB4182','53DE02D9','066A60DC','7365E65C','66D8EBF7','AD72E497','99D92DC5','C15E3B5B','3C171A1F','B1BC332C','BF92E52E','25EA1B4A','3B084592','E16F2C09','BE022780','41B55E80','063EE708','0484041F','FF2BC991','6CCD2B31','680C125B','C581D0A3','55AD0D62','6D0847E6','2ACBDC96','091D4517','867CA719','F7ACFD80','B17460C2','D8F0BF9A','39BCD912','80AC9B8F','A28D8476','0AF7FA0D','FDEEE3AB','EB01014F','0E64A864','070B2A5C','41FC5EA9','E57DE3D4','2E51F9B7','E011B843','BEE06B43','9EBB67D3','CF0DF746','15CF6367','9C36BF4B','211CAE13','AFE54839','5711A76C','417C89E5','EE2601C0','F49EAB8A','1F698C77','BE82E557','A0AE98B0','D1AEA1F0','4BC420B0','4A4AA457','2A28C180','ABD2541D','30DFB0B4','1B534A66','8EFDA394','ECE9089C','FB8FFE3B','F8AABC1C','FF54DCE8','7B3D36AE','EB6E542A','33DB699E','F8AA73DE','D868CFC3','0DFE385A','BFAC61F7','936B20CB','EB216C1D','C379A293','E1411931','18977D31','6BFE1B44','2A349642','B9FFBC0E','57FA67CC','D2737457','7448E2E1','C30784E9','D04B5ECA','014B8BE7','8B5578F7','EB60627D','4E6F2DDA','692A01E5','1D307915','588C54C6','723D971D','FABCAB1F','2E05744F','D0A04158','7042552B','D3771781','49B52858','1B0E7731','2AC5C78F','AB077ADF','CB97F472','A07A11B9','A2A7B73E','A4449DD1','7CA18A4B','107FC715','B94CC1AC','6C0A3639','2443EBF9','9BA19D75','2A728C94','B3FF567E','25AEBFF2','E5C48D10','22C3E437','6A8013E1','4D47341B','CC35E7C6','B11E2DC1','3DA30794','C63F3645','162D71BC','24AF7A3C','3BB09B86','97DEFFE6','E50C3E22','697FF4FB','E4EA94E1','F1765637','1238898C','635DE7ED','201A3937','C04342FE','2D1DFF4B','7E016EDC','8134F92E','04FA0D27','23C30990','B9BBAA40','D1461B30','D9588F82','3384FF98','15DC4CB0','40AFAC6A','BA32B9CB','7F0AF057','8F2D97EA','A8918B5D','0E9A60CC','2E73703D','0E2C8D37','CE46B6AA','A4DCEBC2','FD76B699','07EEAF05','DC0D4A0A','13DE26AB','B9E0A89A','005DA2A7','85037BE1','470D95A5','FD4F6691','301F0B2F','3A1BFB90','61562E99','7800D9B6','C36E83EE','3B2C8C4A','06E91876','D1D4709F','1D53CAE3','5B40BA6F','1D1EFBFF','7E04ACCF','E6FD56CD','84D12C22','448FAAAB','792B58A7','FE132AF0','BF0E2D3C','0C21584F','337DA65B','00D0BE3C','BF199350','56E3EB55','9ABFA924','9BF3018F','63FA05F8','524008A3','$%XX#abcde','AEFB0ADD','485644C2','ADF3F3D1','16767F71','67A60030','DF65A536','8AC4A12F','2603DBD4','757A4647','3A5BC082','61FEF794','DA9F83CF','401E6FED','A12888AC','0E4ECC5C','EC8F059B','B55A1287','C6A8D580','058F2D75','957B6FFF','8797F10A','ADEA6E39','9CDD77BB','1CBF8738','283C1E20','8775280C','C3FFAF23','6D27DCC9','E3D14B94','033606A8','EE7548A6','9910561D','BFBD8C17','0BFDBF8E','D781472A','091ECD13','B3109128','AEA1DA35','E2906045','FBAFC091','FD5D85B8','5E13C841','F192ECE0','B60EFC8A','10D6AC91','168A1B35','A3DFA6BD','1261F98C','8536B115','1628470A','8A057B1A','B8E44E20','941D0063','202016C9','65D1769E','96489634','8C68D788','F0FEFB54','BB8D4185','9DF1D0A2','461657AB','3A327EE1','8308426B','47CABAA0','B87E4209','6F039CA3','CD6230CD','8D83AC1A','DAC59685','939659E7','667B2011','94B36228','8C71E9D4','D5BA10AA','16592165','0C984928','D609EC6D','73DD5BDE','FE99268E','0A11D76E','95155A89','39A0F91F','2B4BD44E','EA07C005','FAD48F86','7A06EB2E','F50A816C','10D6C3D9','C54D2BCD','1B2753E2','8DEC366F','E6C12444','22534120','41BE729A','63552A5E','8FBD7183','153E8629','CE533540','6EE55908','1992FAF9','5F431B5C','A3700152','F47EABC6','1ED84027','349F2EC2','D1E07863','A2ABCC9E','E3A7DE72','0D08FA56','BB58046E','E67E1241','6DC07E65','BFD82AA4','45125E76','11E3D2E8','63C1156F','6C5690F4','A82244FC','2BB0B73A','16FA303F','50A389E0','75A477A7','8F517361','7B13ADB1','42D7FBDA','D35A8E2C','81AC882D','3E0E9789','3F9ADF1C','BD525C16','18C7F070','48BE040C','A9A456A0','C7188F68','371DD874','DE067925','FC6AA78D','C0098A96','6E67BA1B','9ACC5912','9EBA0497','B5DC664F','0E7F9C75','41E25D7D','8AC91F09','0B839C15','59A4E559','941F26AE','36EEF028','465E532E','834E94D4','F86A73F6','96EE1995','6DB2557D','3DD1E3A1','2E8D71AC','A196A822','4DB27C4A','71DCF35F','BDB5C9BF','32DEFD75','9303C764','3A663C82','4E776EDE','70D2B4B6','EF28E5BB','29AAFB54','6B264440','AD23B39E','66A2F628','8691235D','D49B50AF','2B5B959C','AB83304A','9BEC7F30','FF260211','A844B6EA','F4DC8E62','B266DF98','B7731773','5CEC212B','F65A4A51','9A0DFCFE','9597A0D0','7B29B115','61DD9138','8463C6B6','F4907EC5','314D0BA7','4F3CED50','30F5722B','A97C38CD','6D124F5C','32CEFDFC','06722494','21EA1683','28957316','D215FAF9','81D56549','C4E00878','48D67FD8','025F4F96','0DA9A804','0024C4D9','745DA497','72BA8679','332AF6A9','105E93B9','89495BA2','E6E98C02','7877C110','44478E00','C458EDF1','EE69FF97','43DC3D47','A311CC9F','1FA626F2','61E4CA6F','A8F35062','55CB28C6','0611EE0A','472343E8','70B6A181','50C836EE','F2C64915','6C5EDD4B','562CEEDE','48828688','DE36299E','4817F556','D817EFA5','0F6C87B9','081ECF1E','D229F720','8B073CBF','94FCEB6B','34FDA5EE','AE3925FC','87AC811A','F15F65FD','7413D81B','4365147D','EB266FED','25BD5382','51009F1D','112472EA','B8BCC8AB','1C6A92E8','E05374DB','599F0F10','5C9C640F','D18D4403','E4DEE985','5C14D7D2','55170AF9','F9ADDF69','DD025092','BE6C2735','6AADE3C3','13CCB013','E79C09E6','41B4315A','A955A2F9','0A89FB9B','66A7C266','F2C26867','A2080491','65FFB23A','D109FB8D','601C8EEE','74699444','16F5801F','832B4B19','9DD3F79D','2BF62FFE','261BE981','037952C2','18BB961E','94943FC7','35BA11F2','8BF9F513','620ECBE0','A8B76B3E','679B063E','E743757F','0CC5CAAF','72B4EFCE','6FE96434','57ACDAB6','20B13701','552B61C8','EBC88DF6','2AA97838','F0E70BA8','EF3F715A','5F6E1397','CD17AD14','D0502877','FB3F516D','8C302EE4','6AAAD396','BBCBDE53','08547506','B94887D0','ABC559C3','F7F6D248','1FA8017E','962946C2','DDA69020','88367F6B','2085F0B5','2E492611','8B805ADF','8709930C','D10B26AF','BA8E7163','573016A3','B48447EE','DED890E8','24251E73','224E86CE','8F1F8BE5','BED3FEDD','259DFEFF','A5143673','4479EC1C','0FE917C5','F52B7588','CB309E8F','470B4F0D','A867DFD2','D7DE3011','5259B52D','05C7697A','808CF1DE','6DEB39C1','C6C4B870','97F18401','12C6214D','A1B9395B','2D29CAA1','A312DCC2','55E34BCC','3C3DB43B','037871A3','C26C675C','250033FD','E753786A','1AF1A81E','02EFDF94','B917CAC6','5E05BCC0','3384F95C','A9315DF4','D83D9287','7EEF6F5B','C1AAA91D','4CA57BE6','3504B63A','8266B58A')

--=================================================================================

SET @END_TIME = GETDATE()

print 'Finished'

INSERT INTO #tableToHoldQueryResults

SELECT id, aClusteredIndexedColumn

FROM #tableWithGeneratedData t1

WHERE t1.aClusteredIndexedColumn = @WhatToLookFor

INSERT INTO #stats

SELECT @TestUsingInClause

, @MAX

, count(*) as [Matching Records]

, @START_TIME as [Start Time]

, @END_TIME as [End Time]

, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]

FROM #tableToHoldQueryResults

Truncate Table #tableToHoldQueryResults

--against non-indexed column -------------------------------------------

print 'Starting ' + @TestAgainstNonIndexedColumn

SET @START_TIME = GETDATE()

SELECT @TestAgainstNonIndexedColumn, * FROM #tableWithStringToQueryFor ttq

INNER JOIN #tableWithGeneratedData t1 on ttq.theString = t1.aNonIndexedColumn

WHERE t1.aNonIndexedColumn = @WhatToLookFor

SET @END_TIME = GETDATE()

print 'Finished'

INSERT INTO #tableToHoldQueryResults

SELECT id, aNonIndexedColumn

FROM #tableWithGeneratedData t1

WHERE t1.aNonIndexedColumn = @WhatToLookFor

INSERT INTO #stats

SELECT @TestAgainstNonIndexedColumn

, @MAX

, count(*) as [Matching Records]

, @START_TIME as [Start Time]

, @END_TIME as [End Time]

, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]

FROM #tableToHoldQueryResults

Truncate Table #tableToHoldQueryResults

--against clustered indexed column -------------------------------------------

print 'Starting ' + @TestAgainstClusteredIndexedColumn

SET @START_TIME = GETDATE()

SELECT @TestAgainstClusteredIndexedColumn, * FROM #tableWithStringToQueryFor ttq

INNER JOIN #tableWithGeneratedData t1 on ttq.theString = t1.aClusteredIndexedColumn

WHERE t1.aClusteredIndexedColumn = @WhatToLookFor

SET @END_TIME = GETDATE()

print 'Finished'

INSERT INTO #tableToHoldQueryResults

SELECT id, aClusteredIndexedColumn

FROM #tableWithGeneratedData t1

WHERE t1.aClusteredIndexedColumn = @WhatToLookFor

INSERT INTO #stats

SELECT @TestAgainstClusteredIndexedColumn

, @MAX

, count(*) as [Matching Records]

, @START_TIME as [Start Time]

, @END_TIME as [End Time]

, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]

FROM #tableToHoldQueryResults

Truncate Table #tableToHoldQueryResults

--against nonclustered indexed column ----------------------------------------

print 'Starting ' + @TestAgainstNonClusteredIndexedColumn

SET @START_TIME = GETDATE()

SELECT @TestAgainstNonClusteredIndexedColumn, * FROM #tableWithStringToQueryFor ttq

INNER JOIN #tableWithGeneratedData t1 on ttq.theString = t1.aNonClusteredIndexedColumn

WHERE t1.aNonClusteredIndexedColumn = @WhatToLookFor

SET @END_TIME = GETDATE()

print 'Finished'

INSERT INTO #tableToHoldQueryResults

SELECT id, aNonClusteredIndexedColumn

FROM #tableWithGeneratedData t1

WHERE t1.aNonClusteredIndexedColumn = @WhatToLookFor

INSERT INTO #stats

SELECT @TestAgainstNonClusteredIndexedColumn

, @MAX

, count(*) as [Matching Records]

, @START_TIME as [Start Time]

, @END_TIME as [End Time]

, CAST(DATEDIFF(MILLISECOND, @START_TIME,@END_TIME) as varchar) + ' milliseconds' as [Time to Run]

FROM #tableToHoldQueryResults

Truncate table #tableToHoldQueryResults

--Display the results--------------------------------------

select *

from #stats

order by id

--clean up-------------------------------------------------

IF OBJECT_ID('tempdb..#tableWithGeneratedData') IS NOT NULL

DROP TABLE #tableWithGeneratedData

IF OBJECT_ID('tempdb..#tableToHoldQueryResults') IS NOT NULL

DROP TABLE #tableToHoldQueryResults

IF OBJECT_ID('tempdb..#tableWithStringToQueryFor') IS NOT NULL

DROP TABLE #tableWithStringToQueryFor

IF OBJECT_ID('tempdb..#stats') IS NOT NULL

DROP TABLE #stats

-----------------------------------------------------------

Spread the love

SQL: comparing values IN vs INNER JOIN

IN Vs INNER JOIN

The Caveats

The Results Are In!

The Way To Go

The SQL Source Code Used

David Lozinski