Is it a bad idea? I need to compare each entry.Key value in a Dictionary of strings to a SQL table. If there is a match, then I pull the data from that row.
I was originally going to use a foreach loop to query each entry.Key value.
I was told by someone that doing this is a bad idea.
Is it bad because I’m doing too many queries?
Each Dictionary could have thousands of values (which probably means thousands of queries)…
Can anybody suggest a better way? (compare the dictionary to a datatable of results?)
3
You can use an IN clause for this.
SELECT someFields FROM yourDictionaryTable WHERE key IN (List of values)
You’ll need to build your list of values as a string with single quotes and commas, like this:
'value1', 'value2', 'value3'
This will give you a single, high-performing SQL query which will return the data set you want.
3
Yes it is, because if you have thousands of records then it will execute thousands of queries which will be heavy on the database.
There are 2 approaches I can think of to avoid this,
-
Use in statement as mentioned by Robert Harvey
One consideration is there is a limit in parameters that can be passed into IN clause in depending on the database. So If you have more than that amount, you might want to split your set of keys to few statments. -
Use a temporary table to insert keys and join it with your table to obtain a result set
It’s not always bad to put queries in loops, but when you can express the “loop logic” in SQL itself, doing it that way is usually more efficient and more readable.
In this case, you can easily use a single query with the IN operator (as Robert said), and that expresses what you’re actually trying to do much better than a for loop of smaller queries. Both you and the SQL query optimizer benefit from that.
1