0

I am using java. I have 2000 key values in object level and have to fetch the corresponding records for that values from the DB. I found temp table advice here, but won't it affect performance? How do I insert those values in a single shot, so that I can reduce the number of DB calls?

Community
  • 1
  • 1
Samurai
  • 843
  • 6
  • 23
  • 44
  • The temporary table is the accepted solution. Why do you think it affects performance? – Daniel Hilgarth Mar 05 '13 at 13:51
  • Example code and values would be useful. –  Mar 05 '13 at 13:51
  • You could user JOINs instead of INs. – user Mar 05 '13 at 13:52
  • If you know that the number of values will always be 2000 (or around that), Evgeniy Dorofeev's solution will be the fastest and pretty easy to code. If the number of values can be higher, there will be a point where the temp table will be faster. – Jonathan Drapeau Mar 05 '13 at 13:56
  • @Daniel How to insert 2000 rows in a single DB hit? Thats what my doubt is.. I can write some logic and split into 1000, 1000 records and do batch update.. It will take 2 DB hits only.. – Samurai Mar 05 '13 at 14:29

3 Answers3

6

You can use 

col1 in (1,2...)  or col1 in (1001, 1002...)
Evgeniy Dorofeev
  • 133,369
  • 30
  • 199
  • 275
1

I'd say the temp table is the way to go. Will it affect performance? Yes, it might be slightly slower than just hardcoding all of the values. But you can't have everything -- if you don't want to hardcode all of the values, you'll have to accept a small decrease in performance. I think the trade-off is satisfactory, particularly since the impact will probably be quite small.

Jeff Rosenberg
  • 3,522
  • 1
  • 18
  • 38
1

for a single in clause - you cant. see discussion here.

radai
  • 23,949
  • 10
  • 71
  • 115