seancorfield / honeysql

Turn Clojure data structures into SQL
https://cljdoc.org/d/com.github.seancorfield/honeysql/CURRENT
1.78k stars 174 forks source link

Hodgepodge of optimizations #546

Closed alexander-yakushev closed 2 months ago

alexander-yakushev commented 2 months ago

Following #545, here's a bunch of further improvements to HoneySQL efficiency. Commits are meaningful, I've included the explanations in the commit descriptions.

Benchmark I've used:

(format {:select [[[:min :recent_views.user_id] :user_id]
                  :model
                  :model_id
                  [[:max [:coalesce :d.view_count :t.view_count]] :cnt]
                  [:%max.timestamp :max_ts]]
         :group-by  [:model :model_id]
         :where     [:and
                     [:= :context "view"]
                     [:in :model #{"dashboard" "table"}]]
         :order-by  [[:max_ts :desc] [:model :desc]]
         :limit     10
         :left-join [[:report_dashboard :d]
                     [:and
                      [:= :model "dashboard"]
                      [:= :d.id :model_id]]
                     [:metabase_table :t]
                     [:and
                      [:= :model "table"]
                      [:= :t.id :model_id]]]}
        {:dialect :ansi})
Before:
Time per call: 50.31 us   Alloc per call: 121,104b
Time per call: 45.95 us   Alloc per call: 121,171b
Time per call: 43.92 us   Alloc per call: 121,104b

After:
Time per call: 35.65 us   Alloc per call: 82,392b
Time per call: 35.39 us   Alloc per call: 82,392b
Time per call: 35.25 us   Alloc per call: 82,392b