title: Faster/efficient alternative to IN clause in custom/native queries in spring data jpa
body: <p>I have a custom query along these lines. I get the list of orderIds from outside. I have the entire order object list with me, so I can change the query in any way, if needed.</p>
  
  <pre><code>@Query("SELECT p FROM Person p INNER JOIN p.orders o WHERE o.orderId in :orderIds)")
  public List&lt;Person&gt; findByOrderIds(@Param("orderIds") List&lt;String&gt; orderIds);
  </code></pre>
  
  <p>This query works fine, but sometimes it may have anywhere between 50-1000 entries in the orderIds list sent from outside function. So it becomes very slow, taking as much as 5-6 seconds which is not fast enough. My question is, is there a better, faster way to do this? When I googled, and on this site, I see we can use ANY, EXISTS: <a href="https://stackoverflow.com/questions/1769366/postgresql-alternative-to-where-in-respective-where-not-in">Postgresql: alternative to WHERE IN respective WHERE NOT IN</a> or create a temporary table: <a href="https://dba.stackexchange.com/questions/12607/ways-to-speed-up-in-queries-under-postgresql">https://dba.stackexchange.com/questions/12607/ways-to-speed-up-in-queries-under-postgresql</a> or join this to VALUES clause: <a href="https://stackoverflow.com/questions/30053186/alternative-when-in-clause-is-inputed-a-lot-of-values-postgresql">Alternative when IN clause is inputed A LOT of values (postgreSQL)</a>. All these answers are tailored towards direct SQL calls, nothing based on JPA. ANY keyword is not supported by spring-data. Not sure about creating temporary tables in custom queries. I think I can do it with native queries, but have not tried it. I am using spring-data + OpenJPA + PostgresSQL.</p>
  
  <p>Can you please suggest a solution or give pointers? I apologize if I missed anything.</p>
  
  <p>thanks,</p>
  
  <p>Alice</p>