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:
C3poRepo.all( from t in C3poEvent, where: t.completed == 0, order_by: [desc: :occurred_at], limit: ^demand, offset: ^offset )
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.