If I were you, I'd try to narrow down where the slowdown is actually occurring. Right now it isn't clear if it is due to the database, or the calculations. My advice is to add more timing points first.

My suspicion would be on your arrays: they might be growing without you knowing it, which would result in the calculations to take ever longer.

Some comments on your code: you seem to indicate it is pseudo code, so take this with a grain of salt.

1. Your $sth_sel_hydro has a placeholder for the offset, but you never execute it here -- do you pass in a suitable value for the placeholder?
2. The line where you construct the IN string mentions a $aref_hydro_id, but I don't see that declared or filled with data -- I assume you meant to use @hydro_id