Fork me on GitHub
#sql
<
2019-04-10
>
ikitommi07:04:42

> If you can’t get the results all in memory, that’s really your only option -- aside from reading explicitly paginated results and potentially using multiple connections (from a pool, presumably)

ikitommi07:04:51

what about this:

(def get-fruits-map-qualified-batch
  (p/compile-batch
    "SELECT name FROM fruit"
    {:connection connection
     :size 3
     :row (p/rs->map)
     :key (p/qualified-key str/lower-case)}))

(get-fruits-map-qualified-batch connection (partial println "-->"))
;--> [#:fruit{:name Apple} #:fruit{:name Banana} #:fruit{:name Orange}]
;--> [#:fruit{:name Peach}]
; 4

ikitommi07:04:04

kind of pagination. Would benefit of the reducible appoarch, now it stops only when everything is read (could be a terabyte of data)…

ikitommi07:04:52

in simple tests, query compilation + realizing the rows into maps at request time seems to be much faster than the reducible way. I guess the difference comes from implementation details, should try reducible in porsas to see how the approaches actually compare perf-wise. Interesting.

ikitommi07:04:55

;; 1100ns
(let [query (p/compile
              "SELECT * FROM fruit"
              {:connection connection
               :row (p/rs->map)
               :key (p/unqualified-key str/lower-case)})]
  (bench!
    (into [] (map :cost) (query connection))))
; => [59 29 139 89]

seancorfield07:04:57

Something you need to consider is that it's not practical or idiomatic in most cases to "compile" a query against a connection just once somewhere at startup and then run that compiled query multiple times -- against the same connection -- based on the code fragments you're showing.

seancorfield07:04:15

It's fine in specific, performance-critical code to do that.

ikitommi07:04:22

It’s not the same connection

ikitommi07:04:29

the connection is passed in at request-time

ikitommi07:04:54

connection can be used to pre-fetch the rs-meta & compile the optimal code to read the row from rs

seancorfield07:04:17

OK, so you need a connection for the compile and then a connection for the "run". Still, it's not practical or idiomatic for most people to "precompile" all their application queries upfront once like that.

seancorfield07:04:03

Our application has thousands of distinct queries, for example. It would be ridiculous to try to precompile all those somehow upfront before the app starts to run.

seancorfield07:04:18

If you have a limited number of queries, sure, it makes sense to split that.

seancorfield07:04:35

But it really doesn't make sense for most applications.

seancorfield07:04:25

We have a huge number of queries that are built conditionally, on the fly. The "precompile" space for those is tiny.

ikitommi07:04:22

I agree, as said, just poking with the perf to see how fast we can go with clojure. We have done some perf-critical apps and have fall-backed to plain jdbc in some of the queries. Maybe something like porsas could be used there instead.

ikitommi07:04:05

but precomping opens up road for specs. We could extract the row specs and generate function specs to those queries.