FEATURE: support prepared statements (#20)

FEATURE: support prepared statements (#20)

Adds support for prepared statements.

conn.prepared.query("select * from table where id = ?", id: 10)
# will be prepared
conn.prepared.query("select * from table where id = ?", id: 11)
# runs faster since the statement is already prepared
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index 6b8e4c7..4abd808 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -20,7 +20,7 @@ jobs:
     name: Ruby ${{ matrix.ruby }}
     services:
       postgres:
-        image: postgres:9.6
+        image: postgres:10
         env:
           POSTGRES_PASSWORD: postgres
         ports:
diff --git a/README.md b/README.md
index 4842360..3b6d7f4 100644
--- a/README.md
+++ b/README.md
@@ -187,6 +187,21 @@ Note, in Postgres streaming is going to be slower than non-streaming options due
 
 Streaming support is only implemented in the postgres backend at the moment, PRs welcome to add to other backends.
 
+## Prepared Statements
+See [benchmark mini_sql](https://github.com/discourse/mini_sql/tree/master/bench/prepared_perf.rb)
+[benchmark mini_sql vs rails](https://github.com/discourse/mini_sql/tree/master/bench/bilder_perf.rb).
+
+By default prepared cache size is 500 queries. Use prepared queries only for frequent queries.
+
+`‍``ruby
+conn.prepared.query("select * from table where id = ?", id: 10)
+
+ids = rand(100) < 90 ? [1] : [1, 2]
+builder = conn.build("select * from table /*where*/")
+builder.where("id IN (?)", ids)
+builder.prepared(ids.size == 1).query # most frequent query
+`‍``
+
 ## I want more features!
 
 MiniSql is designed to be very minimal. Even though the query builder and type materializer give you a lot of mileage, it is not intended to be a fully fledged ORM. If you are looking for an ORM I recommend investigating ActiveRecord or Sequel which provide significantly more features.
diff --git a/bench/builder_perf.rb b/bench/builder_perf.rb
new file mode 100644
index 0000000..ebed532
--- /dev/null
+++ b/bench/builder_perf.rb
@@ -0,0 +1,89 @@
+# frozen_string_literal: true
+
+require 'bundler/inline'
+
+gemfile do
+  source 'https://rubygems.org'
+  gem 'pg', github: 'ged/ruby-pg'
+  gem 'mini_sql', path: '../'
+  gem 'activerecord'
+  gem 'activemodel'
+  gem 'benchmark-ips'
+  gem 'pry'
+end
+
+require 'active_record'
+require 'benchmark/ips'
+require 'mini_sql'
+
+require '../mini_sql/bench/shared/generate_data'
+
+ar_connection, _ = GenerateData.new(count_records: 10_000).call
+MINI_SQL = MiniSql::Connection.get(ar_connection.raw_connection)
+
+def mini_sql(is_prepared, user_id)
+  MINI_SQL
+    .build(<<~SQL)
+      /*select*/ from topics /*join*/ /*where*/ /*group_by*/
+    SQL
+    .select('users.first_name, count(distinct topics.id) topics_count')
+    .join('users on user_id = users.id')
+    .join('categories on category_id = categories.id')
+    .where('users.id = ?', user_id)
+    .group_by('users.id')
+    .prepared(is_prepared)
+    .query
+end
+
+def ar_prepared(user_id)
+  Topic
+    .select(User.arel_table[:first_name] , Topic.arel_table[:id].count)
+    .joins(:user, :category)
+    .where(user_id: user_id)
+    .group(User.arel_table[:id])
+    .load
+end
+
+def ar_unprepared(user_id)
+  Topic
+    .select('users.first_name, count(distinct topics.id) topics_count')
+    .joins(:user, :category)
+    .where(user_id: user_id)
+    .group('users.id')
+    .load
+end
+
+Benchmark.ips do |x|
+  x.report("mini_sql_prepared") do |n|
+    while n > 0
+      mini_sql(true, rand(100))
+      n -= 1
+    end
+  end
+  x.report("mini_sql") do |n|
+    while n > 0
+      mini_sql(false, rand(100))
+      n -= 1
+    end
+  end
+  x.report("ar_prepared") do |n|
+    while n > 0
+      ar_prepared(rand(100))
+      n -= 1
+    end
+  end
+  x.report("ar_unprepared") do |n|
+    while n > 0
+      ar_unprepared(rand(100))
+      n -= 1
+    end
+  end
+
+  x.compare!
+end
+
+# Comparison:
+#    mini_sql_prepared:     8386.2 i/s
+#             mini_sql:     2742.3 i/s - 3.06x  (± 0.00) slower
+#          ar_prepared:     1599.3 i/s - 5.24x  (± 0.00) slower
+#        ar_unprepared:      868.9 i/s - 9.65x  (± 0.00) slower
diff --git a/bench/prepared_perf.rb b/bench/prepared_perf.rb
new file mode 100644
index 0000000..c0f296c
--- /dev/null
+++ b/bench/prepared_perf.rb
@@ -0,0 +1,60 @@
+# frozen_string_literal: true
+
+require 'bundler/inline'
+
+gemfile do
+  source 'https://rubygems.org'
+  gem 'pg', github: 'ged/ruby-pg'
+  gem 'mini_sql', path: '../'
+  gem 'activerecord'
+  gem 'activemodel'
+  gem 'benchmark-ips'
+  gem 'pry'
+end
+
+require 'active_record'
+require 'benchmark/ips'
+require 'mini_sql'
+
+require '../mini_sql/bench/shared/generate_data'
+
+ar_connection, _ = GenerateData.new(count_records: 10_000).call
+MINI_SQL = MiniSql::Connection.get(ar_connection.raw_connection)
+
+
+sql = <<~SQL
+  select users.first_name, count(distinct topics.id) topics_count
+  from topics
+  inner join users on user_id = users.id
+  inner join categories on category_id = categories.id
+  where users.id = ?
+  group by users.id
+SQL
+
+Benchmark.ips do |x|
+  x.report("ps") do |n|
+    while n > 0
+      MINI_SQL.prepared.query(sql, rand(100))
+      n -= 1
+    end
+  end
+  x.report("without ps") do |n|
+    while n > 0
+      MINI_SQL.query(sql, rand(100))
+      n -= 1
+    end
+  end
+
+  x.compare!
+end
+
+# Warming up --------------------------------------
+#                   ps     1.008k i/100ms
+#           without ps   284.000  i/100ms
+# Calculating -------------------------------------
+#                   ps     10.287k (± 4.2%) i/s -     51.408k in   5.006807s
+#           without ps      2.970k (± 5.3%) i/s -     15.052k in   5.083272s
+#
+# Comparison:
+#                   ps:    10287.2 i/s
+#           without ps:     2970.0 i/s - 3.46x  (± 0.00) slower
\ No newline at end of file
diff --git a/bench/shared/generate_data.rb b/bench/shared/generate_data.rb
index 0e446c2..8622988 100644
--- a/bench/shared/generate_data.rb
+++ b/bench/shared/generate_data.rb
@@ -1,9 +1,12 @@
 # frozen_string_literal: true
 
 class GenerateData
-  class Topic < ActiveRecord::Base; end
-  class User < ActiveRecord::Base; end
-  class Category < ActiveRecord::Base; end
+  class ::Topic < ActiveRecord::Base;
+    belongs_to :user
+    belongs_to :category
+  end
+  class ::User < ActiveRecord::Base; end
+  class ::Category < ActiveRecord::Base; end
 
   def initialize(count_records:)
     @count_records = count_records
diff --git a/lib/mini_sql.rb b/lib/mini_sql.rb
index e156ba4..326cb16 100644
--- a/lib/mini_sql.rb
+++ b/lib/mini_sql.rb
@@ -15,24 +15,33 @@ require_relative "mini_sql/result"
 module MiniSql
   if RUBY_ENGINE == 'jruby'
     module Postgres
-      autoload :Connection, "mini_sql/postgres_jdbc/connection"
+      autoload :Connection,        "mini_sql/postgres_jdbc/connection"
       autoload :DeserializerCache, "mini_sql/postgres_jdbc/deserializer_cache"
     end
   else
     module Postgres
-      autoload :Coders, "mini_sql/postgres/coders"
-      autoload :Connection, "mini_sql/postgres/connection"
-      autoload :DeserializerCache, "mini_sql/postgres/deserializer_cache"
+      autoload :Coders,             "mini_sql/postgres/coders"
+      autoload :Connection,         "mini_sql/postgres/connection"
+      autoload :DeserializerCache,  "mini_sql/postgres/deserializer_cache"
+      autoload :PreparedConnection, "mini_sql/postgres/prepared_connection"
+      autoload :PreparedCache,      "mini_sql/postgres/prepared_cache"
+      autoload :PreparedBinds,      "mini_sql/postgres/prepared_binds"
     end
 
     module Sqlite
-      autoload :Connection, "mini_sql/sqlite/connection"
-      autoload :DeserializerCache, "mini_sql/sqlite/deserializer_cache"
+      autoload :Connection,         "mini_sql/sqlite/connection"
+      autoload :DeserializerCache,  "mini_sql/sqlite/deserializer_cache"
+      autoload :PreparedCache,      "mini_sql/sqlite/prepared_cache"
+      autoload :PreparedBinds,      "mini_sql/sqlite/prepared_binds"
+      autoload :PreparedConnection, "mini_sql/sqlite/prepared_connection"
     end
 
     module Mysql
-      autoload :Connection, "mini_sql/mysql/connection"

[... diff too long, it was truncated ...]

GitHub sha: 197bcbf9

1 Like

This commit appears in #20 which was merged by SamSaffron.