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
-
Select * from table where table.key in (your,values,separated,by,comma)– thepackerJan 19, 2015 at 19:58
-
5Using loops in SQL often leads to "row by agonizing row" processing, which can often be incredibly inefficient.– Dan PichelmanJan 19, 2015 at 20:03
-
2If the list of values is large, consider creating a temporary table with the IDs and then joining the temporary table to the permanent one. If your list is only a few 100 an IN clause will suffice.– Jon RaynorJan 19, 2015 at 20:58
Add a comment
|
3 Answers
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.
answered Jan 19, 2015 at 19:59
-
so are you saying I should convert the Key values of my Dictionary to a comma separated string, and then use the IN clause to have one query? (as opposed to a whole lot of single queries)– terbubbsJan 19, 2015 at 20:03
-
-
2This even works for relative large datasets (only limited by the maximum query length defined by
max_allowed_packet--> stackoverflow.com/questions/16335011/…)– s1lv3rJan 19, 2015 at 22:27
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
answered Jan 20, 2015 at 1:00
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.
-
Loop logic (cursors) in SQL is a performance red flag. Always strive for 'Set' operations in SQL. Sometimes this is not possible and leads to RBAR (row by agonizing row) processing. RBAR is also the least efficient means of doing something. Jan 19, 2015 at 21:01