How to optimize query like select * from orgnizer where orgnizer.id in (a list)

#1

Hi All,

I meet a situation where I get a list of activities from third party service and the I need to fetch corresponding organizers which involve the activities. The code looks like:

    List<Long> activityOrganizerIds = getFromExternalSide();
    ... res = organizerService.findOrganizers(activityOrganizerIds);
    ...
    @Repository
    public interface OrgonizerRepository extends JpaRepository<Orgonizer,Long> {

        @Query("select * from organizer o where o.id in :ids")
        List<Organizer> findOrganizers(List<Long> ids);
        ...
        
    When   activityOrganizerIds size reachs 400 around, the find query is very slow and I have to directly populate  activityOrganizerIds into a temp table and
    use  join table query for the results. The JdbcTemplate query looks like:
    
    "truncate table tmp_activity_organizer"
    "insert into tmp_activity_organizer (organizer_id) values(?)" with JdbcTemplate batch operation and then
    "select o.organizer_id, ...  from organizer  o " +
    "inner join tmp_activity_organizer.organizer_id  ta on o.organizer_id = ta.organizer_id";

With above SQL statements and indexies built for both organizer id and tmp_activity_organizer id, the formance speed up.
Is there any way to optimize the in clause so that I won’t write above ugly code and hibernate or JPA could do the same thing or better in behind?’