sql vs hashtable.containskey vs string.contains

Question

I have a list of values extracted from a sql query in order to find out if x provided values are present in it.

SQL:
  -Select null from table where code='x'
  -resul.count>0

String:
  -Loop for concatenating the codes in one string
  -codesstring.Contains("x")

Hashtable:
  -Loop for adding the codes to the hashtable
  -codeshashtable.ContainsKey("x")

The list will be in the thousands range...whats the fastest way?

Answer 1

You do NOT ask for the same in query 1 and 2.

Strng would be

codestring.Equals ('x')

What is the fastest depends. String loop: no

Hashtable - yes, for thousands already loaded (i.e. you dont have to hit the database)

Otherwise directly database.

Answer 2

Only the SQL will prevent the entire result set from being transferred from the DB to your app. At that point, it depends (a little) on how often you are doing these checks. If you have lots and lots and lots of values to check, then I'd go with the hashtable and cache the entire list in memory.

The string lookup is going to be a poor performer in any scenario.

Answer 3

I may be misunderstanding you, but the fastest way would be to just retrieve the count itself:

select count(*) 
from MyTable 
where code = 'x'