How to ... rails? #38
Comments
|
Can you include and specific examples of what you did and a repro. It is extremely unexpected for mini_sql to be slower than rails under any conditions, for identical queries. |
|
When I create the same benchmark in a separate the benchmark results look different, mini_sql is faster but most time is spent in the query. I haven't been able to understand what I did wrong in my original benchmark also I not able to reproduce it (very sorry to report on something which looks like a false positive).
# frozen_string_literal: true
require "benchmark/ips"
QUERY = <<~SQL
WITH
latest_descriptions AS (
SELECT DISTINCT ON (paycheck_lines.number)
paycheck_lines.number AS number,
paycheck_lines.description AS description
FROM paycheck_lines
INNER JOIN paychecks ON paychecks.id = paycheck_lines.paycheck_id
ORDER BY paycheck_lines.number, paycheck_lines.id DESC
)
SELECT
paycheck_lines.number AS number,
latest_descriptions.description AS description,
paycheck_lines.calculation_type::TEXT AS calculation_type,
paycheck_lines.car_registration_id AS car_registration_id,
paycheck_lines.project_id AS project_id,
paycheck_lines.department_id AS department_id,
SUM(paycheck_lines.sum) AS sum
FROM paychecks
INNER JOIN paycheck_lines ON paycheck_lines.paycheck_id = paychecks.id
INNER JOIN latest_descriptions ON latest_descriptions.number = paycheck_lines.number
WHERE
paychecks.id IN (:paychecks_ids) AND
paycheck_lines.sum != 0
GROUP BY
paycheck_lines.number,
latest_descriptions.description,
paycheck_lines.calculation_type,
paycheck_lines.car_registration_id,
paycheck_lines.project_id,
paycheck_lines.department_id
SQL
MINI_SQL = MiniSql::Connection.get(ActiveRecord::Base.connection.raw_connection)
require "benchmark/ips"
puts "one"
paychecks_ids = [Paycheck.last.id]
Benchmark.ips do |r|
r.warmup = 2
r.time = 15
r.report("org") do
sum = 0.to_d
count = 0
ApplicationRecord
.raw_query(QUERY, paychecks_ids: paychecks_ids)
.to_a
.each do |entry|
count += 1
sum += BigDecimal(entry["sum"])
end
puts "sum #{sum} by count #{count}" if sum != "479000.0".to_d || count != 2
end
r.report("mini") do
sum = 0.to_d
count = 0
MINI_SQL.query(QUERY, paychecks_ids: paychecks_ids).each do |entry|
count += 1
sum += entry.sum
end
puts "sum #{sum} by count #{count}" if sum != "479000.0".to_d || count != 2
end
r.compare!
end
puts "all"
paychecks_ids = Paycheck.all.ids
Benchmark.ips do |r|
r.warmup = 2
r.time = 15
r.report("org") do
sum = 0.to_d
count = 0
ApplicationRecord
.raw_query(QUERY, paychecks_ids: paychecks_ids)
.to_a
.each do |entry|
count += 1
sum += BigDecimal(entry["sum"])
end
puts "sum #{sum} by count #{count}" if sum != "44611441.68".to_d || count != 49
end
r.report("mini") do
sum = 0.to_d
count = 0
MINI_SQL.query(QUERY, paychecks_ids: paychecks_ids).each do |entry|
count += 1
sum += entry.sum
end
puts "sum #{sum} by count #{count}" if sum != "44611441.68".to_d || count != 49
end
r.compare!
end
# one
# Warming up --------------------------------------
# org 3.000 i/100ms
# mini 3.000 i/100ms
# Calculating -------------------------------------
# org 35.148 (±14.2%) i/s - 519.000 in 15.051179s
# mini 34.610 (±14.4%) i/s - 504.000 in 15.047748s
#
# Comparison:
# org: 35.1 i/s
# mini: 34.6 i/s - same-ish: difference falls within error
#
# all
# Warming up --------------------------------------
# org 1.000 i/100ms
# mini 2.000 i/100ms
# Calculating -------------------------------------
# org 8.700 (±57.5%) i/s - 95.000 in 15.089359s
# mini 10.997 (±45.5%) i/s - 124.000 in 15.102766s
#
# Comparison:
# mini: 11.0 i/s
# org: 8.7 i/s - same-ish: difference falls within errorMy question on how It's recommended to hook it up with rails still stands. I would assume I should use the same connection for both AR and MiniSql in each request. |
|
Yes, have a look at Discourse source for an example, we should add to the
readme
…On Wed, 24 Mar 2021 at 8:00 pm, Simon ***@***.***> wrote:
When I create the same benchmark in a separate the benchmark results look
different, mini_sql is faster but most time is spent in the query. I
haven't been able to understand what I did wrong in my original benchmark
also I not able to reproduce it (very sorry to report on something which
looks like a false positive).
one
Warming up --------------------------------------
org 3.000 i/100ms
mini 3.000 i/100ms
Calculating -------------------------------------
org 35.148 (±14.2%) i/s - 519.000 in 15.051179s
mini 34.610 (±14.4%) i/s - 504.000 in 15.047748s
Comparison:
org: 35.1 i/s
mini: 34.6 i/s - same-ish: difference falls within error
all
Warming up --------------------------------------
org 1.000 i/100ms
mini 2.000 i/100ms
Calculating -------------------------------------
org 8.700 (±57.5%) i/s - 95.000 in 15.089359s
mini 10.997 (±45.5%) i/s - 124.000 in 15.102766s
Comparison:
mini: 11.0 i/s
org: 8.7 i/s - same-ish: difference falls within error
# frozen_string_literal: true
require "benchmark/ips"
QUERY = <<~SQL
WITH
latest_descriptions AS (
SELECT DISTINCT ON (paycheck_lines.number)
paycheck_lines.number AS number,
paycheck_lines.description AS description
FROM paycheck_lines
INNER JOIN paychecks ON paychecks.id = paycheck_lines.paycheck_id
ORDER BY paycheck_lines.number, paycheck_lines.id DESC
)
SELECT
paycheck_lines.number AS number,
latest_descriptions.description AS description,
paycheck_lines.calculation_type::TEXT AS calculation_type,
paycheck_lines.car_registration_id AS car_registration_id,
paycheck_lines.project_id AS project_id,
paycheck_lines.department_id AS department_id,
SUM(paycheck_lines.sum) AS sum
FROM paychecks
INNER JOIN paycheck_lines ON paycheck_lines.paycheck_id = paychecks.id
INNER JOIN latest_descriptions ON latest_descriptions.number = paycheck_lines.number
WHERE
paychecks.id IN (:paychecks_ids) AND
paycheck_lines.sum != 0
GROUP BY
paycheck_lines.number,
latest_descriptions.description,
paycheck_lines.calculation_type,
paycheck_lines.car_registration_id,
paycheck_lines.project_id,
paycheck_lines.department_id
SQL
MINI_SQL = MiniSql::Connection.get(ActiveRecord::Base.connection.raw_connection)
require "benchmark/ips"
puts "one"
paychecks_ids = [Paycheck.last.id]
Benchmark.ips do |r|
r.warmup = 2
r.time = 15
r.report("org") do
sum = 0.to_d
count = 0
ApplicationRecord
.raw_query(QUERY, paychecks_ids: paychecks_ids)
.to_a
.each do |entry|
count += 1
sum += BigDecimal(entry["sum"])
end
puts "sum #{sum} by count #{count}" if sum != "479000.0".to_d || count != 2
end
r.report("mini") do
sum = 0.to_d
count = 0
MINI_SQL.query(QUERY, paychecks_ids: paychecks_ids).each do |entry|
count += 1
sum += entry.sum
end
puts "sum #{sum} by count #{count}" if sum != "479000.0".to_d || count != 2
end
r.compare!
end
puts "all"
paychecks_ids = Paycheck.all.ids
Benchmark.ips do |r|
r.warmup = 2
r.time = 15
r.report("org") do
sum = 0.to_d
count = 0
ApplicationRecord
.raw_query(QUERY, paychecks_ids: paychecks_ids)
.to_a
.each do |entry|
count += 1
sum += BigDecimal(entry["sum"])
end
puts "sum #{sum} by count #{count}" if sum != "44611441.68".to_d || count != 49
end
r.report("mini") do
sum = 0.to_d
count = 0
MINI_SQL.query(QUERY, paychecks_ids: paychecks_ids).each do |entry|
count += 1
sum += entry.sum
end
puts "sum #{sum} by count #{count}" if sum != "44611441.68".to_d || count != 49
end
r.compare!
end
# one
# Warming up --------------------------------------
# org 3.000 i/100ms
# mini 3.000 i/100ms
# Calculating -------------------------------------
# org 35.148 (±14.2%) i/s - 519.000 in 15.051179s
# mini 34.610 (±14.4%) i/s - 504.000 in 15.047748s
#
# Comparison:
# org: 35.1 i/s
# mini: 34.6 i/s - same-ish: difference falls within error
#
# all
# Warming up --------------------------------------
# org 1.000 i/100ms
# mini 2.000 i/100ms
# Calculating -------------------------------------
# org 8.700 (±57.5%) i/s - 95.000 in 15.089359s
# mini 10.997 (±45.5%) i/s - 124.000 in 15.102766s
#
# Comparison:
# mini: 11.0 i/s
# org: 8.7 i/s - same-ish: difference falls within error
My question on how It's recommended to hook it up with rails still stands.
I would assume I should use the same connection for both AR and MiniSql in
each request.
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
<#38 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAABIXM6IYFZZ3LRR7Z4LUTTFGS27ANCNFSM4ZTESCPQ>
.
|
|
I'm curious how you are connecting to the the db are you using you rails connection pool or connecting to the adapaer? To use the applications connection pool you would create a ruby file such as. # in lib/sample/db.rb
require 'mini_sql'
module Sample
def self.db
pool = ActiveRecord::Base.connection_pool
conn = pool.active_connection? ? pool.connection : ActiveRecord::Base.establish_connection(Rails.env.to_sym).connection
MiniSql::Connection.get(conn.raw_connection)
end
endAnd call the code similar to the following: sql = "SELECT * FROM users WHERE id < 10"
Sample.db.query(sql)My team is using mini_sql with a number of our rails applications (not for all queries, but ones we do need back fast); some using the connection pool and some don't. But even when using the rails ActiveRecord connection pool to fire queries with mini_sql it's usually at least 50% faster. This is because of the way ActiveRecord maps all the database attributes for the table and then type casts all the resulting values. MiniSql doesn't do this and allows the data returned to be consumed faster. |
|
Sorry for the late response. I did copy the setup from Discourse but removed some parts I don't think we want. We ended up not spending more time tring mini_sql. I think we would have used it if we understand what we need to setup and why. I can't remeber now if this was a problem or not. We have to use the same connection as AR so when we open a transaction in AR, insert some data, then query the data is for both sql apis, don't know if you have had to deal with that? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have some custom queries in by existing rails application. It would have been nice to work withobject instead of hashes. I tries you use mini_sql but then by query became 4-6 times slower. I think It might have something to do with how I hooked it up.
Is there a best practice for how to hook this up with rails?
The text was updated successfully, but these errors were encountered: