I need help in optimizing a part of my server-side Java program. I'll try to simplify the explanation as much as I can.
I have a Java server that needs to maximize a function of A,B,C three objects. I always find the least fitting of the three and replace it with another random one found in Database.
For about 100 iterations it takes about 2.5 seconds and for 250 I already get to 9 seconds.
I tried to find what takes the most time and I lose about 70% of the time on JPA queries. I think the problem is that I execute a query for getting the random new Object each iteration.
Query q = em.createQuery("SELECT object FROM Obj o WHERE .... ORDER BY random()");
Setting parameters
q.setFirstResult(0)
q.setMaxResults(1)
List<Obj> myMcb = q.getResultList();
Where Obj can be either A, B or C. Each type has it's own table in the Postgres DB.
Would the solution of grabbing a lot of data from a single query and then making the "search" inside a Java List work? Would there be a way to make use of caching in JPA or multithreading in Java? Any other ideas about my approach? It is kind of improvised, being the first time I implement something like this.
I would try to guess an id.
Change your select statement to
SELECT object FROM Obj o WHERE id = random(select max(id) from obj)
This must not find an object, but it could and would be much faster. The correct random function is explained here: http://www.techonthenet.com/postgresql/functions/random.php
Of course you could create a List with all existing IDs from Obj and pick one after another randomly for your query. This will work if the number of IDs is small enough to keep all IDs in memory. You could estimate this very easy when count four bytes for every ID.
java.util.List<Integer> idList = em.createQuery("SELECT id FROM Obj).getResultList();
java.util.Collections.shuffle(idList)