Cool Sub-Selects in korma
I was doing some selects from a postgres database into clojure using korma, and they were pretty straight-forward for a master/detail scheme:
(defn locations-for-demand-set [demand-set-id] (select locations (with demands) (fields "locations.*") (where {:demands.demand_set_id demand-set-id})))
and it was working pretty well. The data was coming back from the database, and everything was OK. But as the database got bigger and bigger, we started to see a real performance penalty. Specifically, the pulling of the locations was taking on the order of 30 seconds for a given demand set. That's too long.
The problem, of course, is that this is implemented as a join, and that's not going to be very fast. What's faster, is a sub-select where we can get all the demand ids for a given demand-set, and then use that with an IN clause in SQL. Thankfully, I noticed that korma had just that capability:
(select locations (where {:demand_id [in (subselect demands (fields :id) (where {:demand_set_id demand-set-id})]}))
Unfortunately, this didn't really give me the kind of speed boost I was hoping for. In fact, it only cut off about a half-second of the 31 sec runtime. Kinda disappointing. But the fact had to be related to the size of the sub-select. It was likely 25,000 elements, and doing an IN on that was clearly an expensive operation.
I like that korma supports this feature, but I need a faster way.