sql: store data in list or constantly ask the database?
i got to a point where im intergrating databases in my java application. Im using postgresql, all is fine, i know how it works and what i have todo..
but now im asking myself this:
I will have lots of worker threads (100+) which all have to do simple tasks, but those simple tasks require data, and you guessed it, the data comes from the database.. now it will need the data every 1-2mins, acutally it needs only the next row which is randomly chosen.
But as these list are pretty large i guess.. contains about 100-200 items, and as they are stored in the RAM memory (this is right or isn't???) i think my 3Gig ram will be eaten in no time.. so i was thinking about something like contacting the database and ask everytime a single row... but this will require memory too, but i guess it will use less then storing all my data in lists, and i the gc will release the memory after the request was succesful(?)
What i want to mention also, is that multiple worker threads use the same list to work...
Now my question is what will be the best..??
btw now that i'm writing this topic, im thinknig about caching resultsets, like caching 20 items out of my 100 items in database.. and if its a request is made and the cachelist already contains it no need, else i will remove the tail of the list and add the new request.. what you guys think???
I know this is pretty advanced, at least i think xD, so if someone has experience with this, post!