Performance during searching data in DB vs Memory

Question

I am currently working with a PostgreSQL database, Spring and Hibernate. I have one table where attribute correlation_id is unique. Each time before I add a new element first I have to check does any item with a new correlation_id already exist in db or not.

For this case I have implemented recursive function that will generate a new correlation_id and check does it exist or not in db. It means this function will make a call on db each time so sometimes it can be just one call but sometimes i could be five, ten or even more. This example is shown in example one.

Example1:

private String generateId() {

    String myId = StaticFunction.generateMyId();
    MyMessages doesExist = MyServiceDaoImpl.checkDoesItExistInDB(myId);
    if(doesExist != null) {
        generateId();
    }

    return myId;
}

In the second example I suppose that I could create just one call to db and retrieve all items and put them into collection. Then I am able to via stream to search for specific item using also recursive function. Example2:

private String generateId(List<MyMessages> messages) {
    String myId = StaticFunction.generateMyId();        

    MyMessages myMessage = messages.stream().filter(m -> 
        m.getCorrelationId.equals(myId)).findFirst().orElse(null);
    if (MyMessages != null) {
        generateId(messages);
    }

    return myId;
}

My question is whats is the best approach to make this thing right? Do you have some other solutions? What are the advantages and disadvantages of above examples?

and a third option would be just to insert any and fail on a duplicate key — Scary Wombat, Jul 20 '17 at 08:08
If you had another id and correlation_id, you could choose whether or not to copy the unique value from `id` to `correlation_id` — Scary Wombat, Jul 20 '17 at 08:17
Another option depending on your load is to use the current time as millis — Scary Wombat, Jul 20 '17 at 08:32
Possible duplicate of https://stackoverflow.com/questions/192920/generating-a-globally-unique-identifier-in-java — Raedwald, Jul 20 '17 at 08:32

score 1 · Answer 1 · answered Jul 20 '17 at 08:12

1

If you cannot use db generated ids, as suggested in the comments, you could use a UUID generator to create the PKs. The probabilities of collision are so low it's not worth checking in the db.

For generating UUIDs in Java take a look at http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html

answered Jul 20 '17 at 08:12

Andres

10,561
4
45
63

1

It's way more probable that you're hit by a meteorite than having a UUID collision – Andres Jul 20 '17 at 08:18
uuid is generated from multiple parameters - node (hostname or ip or mac), timestamp, counter.. - you can pretty much rely that uuid will be unique and just retry/fail if you hit the duplicate key exception. I'd bet 1:2^256 you will be ok.. – gusto2 Jul 20 '17 at 08:25
it is unacceptable " - then let the dataabse deal with the unique keys and it will ensure the generated value is unique. – gusto2 Jul 20 '17 at 08:27

score 0 · Answer 2 · answered Jul 20 '17 at 08:17

There's nothing wrong with case 1, DB can do lookups very effeciently when the column is indexed. But - you need to do the DB access.

The second case looks much faster (iterate in memory would be much faster than any DB access), however it has drawbacks: You have to keep all your messages (or at least their correlation ids) in memory and when having A LOT of data, you're scr.. you will have bad time to fix it

As well consider scalability where multiple instances of your application could access a DB.

Therefore I'd suggest to let the database generate the key (you can use e.g. SERIAL data type) and Hibernate returns the generated keys when saving the object. If you need custom ids (generated by your app), you can use uuid where there's low probability of the value conflict

As well you can use UPSERT syntax (INSERT .... ON CONFLICT (correlation_id) ...)

Have fun

Performance during searching data in DB vs Memory

2 Answers2