current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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?)

share|improve this question
    
Select * from table where table.key in (your,values,separated,by,comma) –  thepacker Jan 19 at 19:58
4  
Using loops in SQL often leads to "row by agonizing row" processing, which can often be incredibly inefficient. –  Dan Pichelman Jan 19 at 20:03
2  
If 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 Raynor Jan 19 at 20:58

3 Answers 3

up vote 8 down vote accepted

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.

share|improve this answer
    
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) –  terbubbs Jan 19 at 20:03
    
Yes, exactly... –  Robert Harvey Jan 19 at 20:06
    
understood. thank you for the help. –  terbubbs Jan 19 at 20:07
1  
This even works for relative large datasets (only limited by the maximum query length defined by max_allowed_packet --> stackoverflow.com/questions/16335011/…) –  s1lv3r Jan 19 at 22:27
up vote 1 down vote

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,

  1. 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.

  2. Use a temporary table to insert keys and join it with your table to obtain a result set

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
    
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. –  Jon Raynor Jan 19 at 21:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.