I have just tried using the new DB Looping Node. I am finding that it suffers the same limitations as legacy approaches; namely that it cannot accept a large list for the IN statement. Therefore a loop of several (or many times) over the database is still necessary, which is of course very slow. Am I missing something? Can a large list be used and loop over the database only once?
Hello @clac2188 ,
what database and settings are you using? What is the error message you get? Some databases have various limitations regarding the statement size e.g. Oracle supports only 1000 values in an IN statement whereas other DBs limit the total size of the statement.
I assume you have adapted the “No of values per query” parameter with a larger number or have enabled the “Read all” option.
Bye
Tobias
Hi Tobias - I am using Oracle database and hitting the 1000 limit because, I guess, the DB Query Reader is doing a passthrough query. When faced with an input list many times larger than 1000, it is quite slow to have to pass a sub list many times to the database. I had hoped that the DB Looping Node would approach this dilemma, but apparently, it does not. Correct?
Hello @clac2188 ,
you are correct. The node works with what the database allows us to do and does not create any special lookup tables or something similar in the backend which might cause problems with missing permissions etc.
Bye
Tobias
1 Like