Java Community | Help. Code. Learn.•2w ago

How can I make this more efficient?

I have this code snippet that finds matching records based on composite key string and it is very slow. Is there a better way to do this? Main method:

private Set<SomeObjectCompositeKey> existingValues(Set<SomeObjectCompositeKey> existingValuesBatched) {
 Set<String> stringCompositeKeys = toStringCompositeKeys(existingValuesBatched);

 return someObjectRepository.findExistingBySomeObjectCompositeKeys(stringCompositeKeys).stream()
 .map(someObject -> new SomeObjectCompositeKey(someObject.getSrcId(), someObject.getType()))
 .collect(Collectors.toSet());
}

private Set<SomeObjectCompositeKey> existingValues(Set<SomeObjectCompositeKey> existingValuesBatched) {
 Set<String> stringCompositeKeys = toStringCompositeKeys(existingValuesBatched);

 return someObjectRepository.findExistingBySomeObjectCompositeKeys(stringCompositeKeys).stream()
 .map(someObject -> new SomeObjectCompositeKey(someObject.getSrcId(), someObject.getType()))
 .collect(Collectors.toSet());
}

toString:

private Set<String> toStringCompositeKeys(Set<SomeObjectCompositeKey> compositeKeys) {
 return compositeKeys.stream().map(SomeObjectCompositeKey::toString).collect(Collectors.toSet());
}

private Set<String> toStringCompositeKeys(Set<SomeObjectCompositeKey> compositeKeys) {
 return compositeKeys.stream().map(SomeObjectCompositeKey::toString).collect(Collectors.toSet());
}

Query:

@Query("SELECT p FROM SomeObject p WHERE CONCAT(p.srcId, ',', p.type) IN :someObjectCompositeKeys")
List<SomeObject> findExistingBySomeObjectCompositeKeys(@Param("someObjectCompositeKeys") Set<String> someObjectCompositeKeys);

@Query("SELECT p FROM SomeObject p WHERE CONCAT(p.srcId, ',', p.type) IN :someObjectCompositeKeys")
List<SomeObject> findExistingBySomeObjectCompositeKeys(@Param("someObjectCompositeKeys") Set<String> someObjectCompositeKeys);

24 Replies

JavaBot•2w ago

⌛ This post has been reserved for your question.

Hey @Kale Vivi! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

straightface•2w ago

how long does sql query take in sql client? @Kale Vivi

Kale ViviOP•2w ago

Like only if i directly call the sql ? I don't know because I need to do this 500K records in batches of 1000

ayylmao123xdd•2w ago

https://tenor.com/view/cat-cat-mute-discord-cat-mute-mute-cat-discord-mute-gif-2227451834433406205

Tenor

ayylmao123xdd•2w ago

is it a requirement that its a batch of 1k what if you try less or more

Kale ViviOP•2w ago

I can try but I think there might be jpa or postgres db limits here? (at least for the more case) Why would less be faster?

ayylmao123xdd•2w ago

na no clue just try it l0l maybe its gonna help

Kale ViviOP•2w ago

I would think it's slower?

ayylmao123xdd•2w ago

are you doing the thing that you asked before btw checking if exists and deleting this ones faster than updating ig

Kale ViviOP•2w ago

Well I mean this is one of the bottlenecks, so tackling this approach first

ayylmao123xdd•2w ago

or you could update do the update query

Kale ViviOP•2w ago

I need to do this first before the update, this is just hte find that is taking forever

ayylmao123xdd•2w ago

update where bla bla bla bla and if it updates nothing so its 0 then you just save so you technically dont even have to find 😱

Kale ViviOP•2w ago

don't I need to pull the records to update them? I'm confused

ayylmao123xdd•2w ago

i mean you can but you can also just use the query if you do a whole resource update you technically wont even have to pull the entity from db but then you wont have locking so thats the con if you wanted to add it

Kale ViviOP•2w ago

I mean I can't just update because I first need to know if it's an update or insert case so that's where this lookup is required first if I jsut do update for everything, I will get fails for insert cases right? where those records don't exist

ayylmao123xdd•2w ago

you can call the update and if record doesnt exist it wont update and you will get 0 as the response 0 rows changed and if its 0 then you call insert so worst case scenario its two queries

Kale ViviOP•2w ago

How do I get this response?

ayylmao123xdd•2w ago

from the update method

Kale ViviOP•2w ago

Can you share an example of the update method?

ayylmao123xdd•2w ago

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

    @Modifying
    @Query("UPDATE Employee e SET e.name = :name WHERE e.id = :id")
    int updateEmployeeName(Long id, String name);
}

int updatedRows = employeeRepository.updateEmployeeName(1L, "New Name");

if (updatedRows > 0) {
    System.out.println("Record was updated.");
} else {
    System.out.println("No record was updated.");
}

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

    @Modifying
    @Query("UPDATE Employee e SET e.name = :name WHERE e.id = :id")
    int updateEmployeeName(Long id, String name);
}

int updatedRows = employeeRepository.updateEmployeeName(1L, "New Name");

if (updatedRows > 0) {
    System.out.println("Record was updated.");
} else {
    System.out.println("No record was updated.");
}

gpt btw not me removed the transactional wasnt needed

Kale ViviOP•2w ago

That doesn't help me because it just returns the number updated or not and not which ones How do I which ones to insert then?

ayylmao123xdd•2w ago

well you would need to call the update as a separate query with each entity additionally you could just delete all by the ids you have and then insert them but im not sure about the times and efficiency

JavaBot•2w ago

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Gaming

Programming

How can I make this more efficient?

Did you find this page helpful?