I’ve got an issue with a 3 stage GenStage pipeline I’ve implemented. In the producer I do a select out of a MS SqlServer database with ecto as such:
from t in C3poEvent,
where: t.completed == 0,
order_by: [desc: :occurred_at],
seems to make sense. The issue I’m having is that is that the database is preparing this statement and executing it, and then the next time data is demanded it prepares a new statement and executes it. The DBA I’m working with says that it should prepare the statement once and then execute it X times rather than re-preparing it again. The issue that I’m seeing because of this is that the sql adaptor runs out of handles after about 1200 demands.
My DBA suggested using raw sql but I really don’t like that idea for a long term solution.
This situation is so arcane that I’m not even sure how to ask this question. The end result that I’m seeing is that after roughly 63K rows the application crashes. I can restart it and it will crash again after the next 63K rows. I’m working against 24 million so this is not a good thing, obviously.
While waiting for a more definitive answer I recommend that you put together a bare-bones elixir/ecto project:
Start retrieving rows with purely sequential code, i.e. stay within a single process - and see whether you can reliably get beyond the 63K mark.
If it still fails, it’s time to try using “hand made SQL” i.e. get as close to the driver as possible - and if that still fails there may be a driver issue.
If the single process works - refactor towards an example with a GenStage producer and a simple consumer. If things start breaking here, post your code - as your producer implementation may be missing something.
Also which version of Ecto and which adapter are you using?