This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2019-04-22
Channels
- # announcements (1)
- # beginners (179)
- # boot (8)
- # calva (3)
- # cider (4)
- # clara (3)
- # cljdoc (31)
- # clojure (9)
- # clojure-austin (1)
- # clojure-chicago (5)
- # clojure-dev (19)
- # clojure-nl (2)
- # clojure-uk (1)
- # clojurescript (13)
- # core-matrix (1)
- # cursive (86)
- # datascript (2)
- # datomic (13)
- # emacs (3)
- # figwheel-main (1)
- # fulcro (66)
- # off-topic (250)
- # pathom (7)
- # re-frame (19)
- # reitit (5)
- # sql (37)
- # uncomplicate (5)
some news from my porsas
spike:
1) Found a (few times) faster way to do ResultSet->Map conversion
2) new create-query
, which just takes the mapping options and return a normal query
function, which creates the actual ResultSet->Map converter on the ~first invocation and stores it in a local cache of sql->ResultSet->Map.
3) new query
which looks ~like next.jdbc
query, but behind the scenes uses the create-query
way
using the caching version:
;; initialize once per application
(def query (p/create-query))
(query connection "SELECT * FROM fruit") ;; "slow"
(query connection "SELECT * FROM fruit") ;; "fast"
(query connection "SELECT * FROM fruit") ;; ...
quick perf test claims that the non-caching version is just bit faster that next.jdbc
(which is many times faster than java.jdbc
), but the cached versions are much faster with all settings.
(defn perf-test []
;; 630ns
(title "java")
(bench! (java-query connection "SELECT * FROM fruit"))
;; 630ns
(let [query (p/create-query {:row (p/rs->map)})]
(title "porsas: compiled & cached query")
(bench! (query connection "SELECT * FROM fruit")))
;; 1400ns
(let [query (p/create-query)]
(title "porsas: cached query")
(bench! (query connection "SELECT * FROM fruit")))
;; 2100ns
(title "porsas: dynamic query")
(bench! (p/query connection "SELECT * FROM fruit"))
;; 3500ns
(title "next.jdbc")
(bench! (jdbc/execute! connection ["SELECT * FROM fruit"]))
;; 6600ns
(title "java.jdbc")
(bench! (j/query {:connection connection} ["SELECT * FROM fruit"])))
@seancorfield would you be interested in supporting the two-phase appoach of porsas in next.jdbc
?
if there would be any support for cache of sql->ResultSet->Map, it should be bounded and inspectable.
the faster RS->Map mapping is done here: https://github.com/metosin/porsas/commit/4f006fc87a304c086dd6e95c31d01fc93d570a68
e.g. PersistentArrayMap
has a constructor, which takes the initial values as an array. Creating a map with 5 fields drops from 300ns to 130ns on my macbook.
with the reduce+assoc, the impl needs to compare keys to see if it’s a new value or not. there should not be duplicates in ResultSets I guess.
Unless you join across multiple tables and don't use aliases in SQL to make them unique.
clojure.java.jdbc
has renaming code to make column names unique. In next.jdbc
I've avoided that as a deliberate tradeoff -- but it shouldn't be needed when qualified keywords are used.
tested what happens if there are duplicates: all get into the map. get
returns the first, but with merge
the last stays. kinda dangerous, if there could be duplicates.
Your rs->map is a bit of a straw man since next.jdbc
uses a transient hash map (if you're building rows). But your weird array map approach might well still be faster -- and it's extensible (in next.jdbc
so you could implement it yourself anyway).
the two-phase is fast because the resultsetmeta & column reading needs to do only once. But it also assumes that the tables don’t change while the application is running. Which is kinda bad assumption (but this is how the staticly typed jdbc libs all need to work, and are fast because of that)
That's an interesting optimization. Pretty sure you could build that with the open builder machinery. Have a read of the docs https://cljdoc.org/d/seancorfield/next.jdbc/1.0.0-alpha8/doc/readme and try it out.
And, yes, there's an official release now.
I haven't announced it yet. Will do that later tonight. There are still caveats about the final resting place and therefore the group/artifact ID.
the https://cljdoc.org/d/seancorfield/next.jdbc/1.0.0-alpha8/doc/getting-started/result-set-builders could have an example implementation to start writing a custom one?
btw, protocol methods can have docs too:
(defprotocol RowBuilder
"Protocol for building rows in various representations"
(->row [_] "called once per row to create the basis of each row")
...)
It links to the tests that have an example.
Based on your record builder 😁
BTW, I'm out at dinner and on my phone so detailed responses are hard.
And, yes, I know about docs on protocol methods -- but they're documented thoroughly at the namespace level.
I plan to keep expanding the docs including adding a full guide on datafy / nav in this context.
have a nice dinner. need to go too, but the quick guess is that the porsas-style cache can’t be added to the current impl of the rowbuilders as the cache is sql-string -> ResultSet -> Value
, would need the String to get the cache working. Your current impl starts with ResultSet. Not sure if there is anything from get the cache id…
You could handle it through extra options. In the general case -- via an arbitrary PreparedStatement you can't get the SQL string anyway I think?