2

I need to select based on a big list:

SELECT ... FROM tb WHERE tb.cl IN (?, ?, ?, ..................many)

I can't do it this way because the driver implementation (Jaybird) limits the query to 1500 parameters max, also I can't select/load all and filter inside the app because there's not enough memory.

What should I do?

Extra info

I'm using Spring Boot with Hibernate and JPA Repositories, so if could be done in JPQL or using some other environment-related technique it would fit nicer.

I will actually use a ...WHERE tb.cl NOT IN..., if it makes any difference.
It's kinda like an EDI, I'm connecting two distinct databases, so using a nested select isn't an option.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Lucas Noetzold
  • 1,670
  • 1
  • 13
  • 29
  • 1
    Why do you want to pass 1,500 values to a `select`? If it is the result of another `select`, then you can do all the work directly in SQL. If there is another source, then create a table with the values. – Gordon Linoff Oct 10 '19 at 01:23
  • I'm making an EDI app for another two, so I'm actually talking about two databases (not even same server tech, one is Firebird and other MySQL) – Lucas Noetzold Oct 10 '19 at 01:25
  • 1
    Jaybird itself doesn't limit the number of items in the IN-list (nor the number of parameters), that are implementation limits of Firebird itself. – Mark Rotteveel Oct 11 '19 at 08:12
  • 1
    Possible duplicate of [Filter sql based on C# List instead of a filter table](https://stackoverflow.com/questions/43997516/filter-sql-based-on-c-sharp-list-instead-of-a-filter-table) – Arioch 'The Oct 11 '19 at 08:34
  • `IN` in Firebird is converted into `X=A OR X=B or X=C or ....` which is quite slow and resources-consuming on long lists. It is not recommended to use long IN-lists in Firebird. See 3 alternative approaches at https://stackoverflow.com/a/43997801/976391 Additionally, if your list is populated from some tree - usually correct query would need only parent IDs without all their children IDs – Arioch 'The Oct 11 '19 at 08:37

1 Answers1

1

One alternative is to use a temporary table and do as many inserts as you need into that table. After that you can use a:

Select .. from tb where tb.cl in (select cl from myTempTable)

and then do a truncate on the temporary table or a drop. As far as I know, all the leading relational databases set a limit in the IN clause if you are using parameters. On the other hand, using a temp table will do the trick.

Gerardo Arroyo
  • 151
  • 5
  • 1
    Firebird supports [global temporary tables](https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-gtt). You only need to define a global temporary table once, and the content is only visible to the current transaction or the current connection depending on the definition (`on commit delete rows` vs `on commit preserve rows`), so there is no need to delete or truncate yourself. – Mark Rotteveel Oct 11 '19 at 08:15
  • it would still not be best approach to use IN even against another table. Tables are to be `JOIN`ed instead. – Arioch 'The Oct 11 '19 at 08:38
  • @Arioch'The why is that? – Lucas Noetzold Nov 18 '19 at 18:11
  • @LucasNoetzold I already told above in the comments to the question. That just does not scale up well in Groton/Interbase/Firebird lineage. – Arioch 'The Nov 19 '19 at 10:30