Một số giải pháp

insert nhiều bản ghi vào cơ sở dữ liệu

Nguyễn Huy Hùng

Education Section, Mar. 03, 2017

1/14

Nội dung

1 Sử dụng duy nhất một transaction

2  Bỏ qua validate khi insert dữ liệu

3 Chèn vào một khối

4 Sử dụng method import

5 So sánh hiệu năng của các giải pháp

2/14

Tạo bảng User có 2 trường name và email để insert dữ liệu

3/14

class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users do |t|
      t.string :name
      t.string :email

      t.timestamps
    end
  end
end

1 Sử dụng duy nhất một transaction

Transaction là một nhóm có thứ tự các hoạt động thao tác trên cơ sở dữ liệu, và được xem như 1 đơn vị thao tác duy nhất

 

Giả sử cần insert 100 bản ghi vào bảng User:

Tạo một hash

 

4/14

data = {name: "bibi", email: "bibi@demo.com"}

1 Sử dụng duy nhất một transaction (2)

a/ Insert theo cách thông thường

Khi đó sẽ tạo ra 100 transaction, tương ứng với việc gọi đến 100 câu lệnh insert trong SQL

5/14

100.times{User.create data}
irb(main):009:0> 100.times{User.create data}
   (0.1ms)  begin transaction
  SQL (0.2ms)  INSERT INTO "users" ("name", "email", "created_at", "updated_at")
VALUES (?, ?, ?, ?)  [["name", "bibi"], ["email", "bibi@demo.com"], ["created_at",
2016-12-28 04:28:25 UTC], ["updated_at", 2016-12-28 04:28:25 UTC]]
   (115.1ms)  commit transaction
.
.
.
   (0.1ms)  begin transaction
  SQL (0.3ms)  INSERT INTO "users" ("name", "email", "created_at", "updated_at")
VALUES (?, ?, ?, ?)  [["name", "bibi"], ["email", "bibi@demo.com"], ["created_at",
2016-12-28 04:28:38 UTC], ["updated_at", 2016-12-28 04:28:38 UTC]]
   (244.5ms)  commit transaction
=> 100
/* SQL */
INSERT INTO models (...) VALUES (...);
INSERT INTO models (...) VALUES (...);
INSERT INTO models (...) VALUES (...);
INSERT INTO models (...) VALUES (...);

1 Sử dụng duy nhất một transaction (3)

b/ Insert thông qua ActiveRecord

 

Khi đó việc insert chỉ phải thực hiện trong 1 transaction, tương ứng với 1 câu lệnh SQL

6/14

ActiveRecord::Base.transaction do 100.times{User.create data} end
irb(main):018:0* ActiveRecord::Base.transaction do 100.times{User.create data} end
   (0.1ms)  begin transaction
  SQL (0.3ms)  INSERT INTO "users" ("name", "email", "created_at", "updated_at")
VALUES (?, ?, ?, ?)  [["name", "bibi"], ["email", "bibi@demo.com"], ["created_at",
2016-12-28 04:40:43 UTC], ["updated_at", 2016-12-28 04:40:43 UTC]]
  SQL (0.2ms)  INSERT INTO "users" ("name", "email", "created_at", "updated_at")
VALUES (?, ?, ?, ?)  [["name", "bibi"], ["email", "bibi@demo.com"], ["created_at",
2016-12-28 04:40:43 UTC], ["updated_at", 2016-12-28 04:40:43 UTC]]
.
.
.
  SQL (0.1ms)  INSERT INTO "users" ("name", "email", "created_at", "updated_at")
VALUES (?, ?, ?, ?)  [["name", "bibi"], ["email", "bibi@demo.com"], ["created_at",
2016-12-28 04:40:43 UTC], ["updated_at", 2016-12-28 04:40:43 UTC]]
   (132.2ms)  commit transaction
=> 100
/* SQL */
INSERT INTO models (...) VALUES
  (...),
  (...),
  ...

2 Bỏ qua validate khi insert dữ liệu

Nếu không phải kiểm tra tính hợp lệ của dữ liệu khi insert thì có thể tăng tốc hệ thống bằng cách chạy trực tiếp câu lệnh SQL

Ví dụ, thực hiện validate name trong model:

7/14

a/ Khi chạy lệnh User.create thì dữ liệu sẽ được validate

b/ Chạy trực tiếp câu lệnh SQL, không validate dữ liệu

class User < ApplicationRecord
  validates :name, presence: true, length: {minimum: 6}
end
irb(main):012:0* User.create! name: "bibi", email: "bibi@demo.com"
   (0.1ms)  begin transaction
   (0.1ms)  rollback transaction
ActiveRecord::RecordInvalid: Validation failed: Name is too short (minimum is 6 characters)
irb(main):020:0> now = Time.current
=> Wed, 28 Dec 2016 06:14:01 UTC +00:00
irb(main):021:0> sql = "INSERT INTO users(name, email, created_at, updated_at)
VALUES('bibi', 'bibi@demo.com', '#{now}', '#{now}')"
=> "INSERT INTO users(name, email, created_at, updated_at) VALUES('bibi',
'bibi@demo.com', '2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC')"
irb(main):022:0> User.connection.execute sql
   (124.0ms)  INSERT INTO users(name, email, created_at, updated_at)
VALUES('bibi', 'bibi@demo.com', '2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC')
=> []

3 Chèn vào một khối

Ý tưởng: nối các chuỗi dữ liệu cần insert thành một chuỗi, sử dụng 1 câu lệnh insert

8/14

irb(main):038:0> data2 = []
=> []
irb(main):039:0> 3.times do data2.push "('qwerty', 'qwerty@demo.com', '#{now}', '#{now}')" end
=> 3
irb(main):040:0> sql = "INSERT INTO users(name, email, created_at, updated_at) VALUES #{data2.join(", ")}"
=> "INSERT INTO users(name, email, created_at, updated_at) VALUES ('qwerty', 'qwerty@demo.com',
'2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC'), ('qwerty', 'qwerty@demo.com',
'2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC'), ('qwerty', 'qwerty@demo.com',
'2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC')"
irb(main):041:0> User.connection.execute sql
   (104.1ms)  INSERT INTO users(name, email, created_at, updated_at) VALUES ('qwerty', 'qwerty@demo.com',
'2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC'), ('qwerty', 'qwerty@demo.com', '2016-12-28 06:14:01 UTC',
'2016-12-28 06:14:01 UTC'), ('qwerty', 'qwerty@demo.com', '2016-12-28 06:14:01 UTC', '2016-12-28 06:14:01 UTC')
=> []

4 Sử dụng method import

Bổ sung gem activerecord-import:

Thêm bản ghi vào cơ sở dữ liệu:

9/14

gem "activerecord-import", ">= 0.2.0"
irb(main):043:0> columns = [:name, :email]
=> [:name, :email]
irb(main):044:0> values = []
=> []
irb(main):045:0> 3.times do values.push ['member', 'member@demo.com'] end
=> 3
irb(main):046:0> User.import columns, values
   (0.1ms)  select sqlite_version(*)
   (0.1ms)  begin transaction
  Class Create Many Without Validations Or Callbacks (0.3ms)  INSERT INTO "users"
("name","email","created_at","updated_at") VALUES ('member','member@demo.com',
'2016-12-28 06:49:15.799046','2016-12-28 06:49:15.799073'),('member','member@demo.com',
'2016-12-28 06:49:15.799046','2016-12-28 06:49:15.799073'),('member','member@demo.com',
'2016-12-28 06:49:15.799046','2016-12-28 06:49:15.799073')
   (0.1ms)  SELECT changes();
   (106.2ms)  commit transaction
=> #<struct ActiveRecord::Import::Result failed_instances=[], num_inserts=1, ids=[828, 829, 830]>

4 Sử dụng method import (2)

Nếu không cần validate dữ liệu thì có thể thêm 'validate : false' vào câu lệnh import

10/14

irb(main):049:0> values2 = []
=> []
irb(main):050:0> 3.times do values2.push ['milu', 'milu@demo.com'] end
=> 3
irb(main):051:0> User.import columns, values2, validate: false
   (0.3ms)  begin transaction
  Class Create Many Without Validations Or Callbacks (0.6ms)  INSERT INTO "users" 
("name","email","created_at","updated_at") VALUES ('milu','milu@demo.com',
'2016-12-28 07:05:06.714266','2016-12-28 07:05:06.714330'),('milu','milu@demo.com',
'2016-12-28 07:05:06.714266','2016-12-28 07:05:06.714330'),('milu','milu@demo.com',
'2016-12-28 07:05:06.714266','2016-12-28 07:05:06.714330')
   (0.2ms)  SELECT changes();
   (118.3ms)  commit transaction
=> #<struct ActiveRecord::Import::Result failed_instances=[], num_inserts=1, ids=[831, 832, 833]>

5 So sánh hiệu năng của các giải pháp

11/14

Tạo file lib/tasks/insert_user.rake để so sánh:

namespace :insert_user do
  task :test => :environment do
    CONN = ActiveRecord::Base.connection
    TIMES = 1

    def do_inserts
      TIMES.times {User.create name: 'framgia', email:'framgia@demo.com'}
    end

    def raw_sql
      TIMES.times {CONN.execute "INSERT INTO users (name, email, created_at, updated_at)"+
        " VALUES('framgia', 'framgia@demo.com', '2016-12-28 06:14:01', '2016-12-28 06:14:01')"}
    end

    def mass_insert
      data = []
      TIMES.times do
        data.push "('framgia', 'framgia@demo.com', '2016-12-28 06:14:01', '2016-12-28 06:14:01')"
      end
      sql = "INSERT INTO users (name, email, created_at, updated_at) VALUES #{data.join(', ')}"
      CONN.execute sql
    end

    def activerecord_extensions_mass_insert validate = false
      columns = [:name, :email]
      values = []
      TIMES.times do
        values.push ['framgia', 'framgia@demo.com']
      end

      User.import columns, values, {validate: validate}
    end

    puts "Testing various insert methods for #{TIMES} inserts\n\n"
    print "*base* ActiveRecord without transaction: "
    start = Time.now.to_f
    do_inserts
    stop = Time.now.to_f
    base = stop.real - start.real
    puts "#{base} seconds\n\n"

    print "1/ ActiveRecord with transaction:"
    start = Time.now.to_f
    ActiveRecord::Base.transaction{do_inserts}
    stop = Time.now.to_f
    diff = stop.real - start.real
    puts sprintf("%2.2fx faster than base\n\n", base / diff)

    print "2/ Raw SQL without transaction: "
    start = Time.now.to_f
    raw_sql
    stop = Time.now.to_f
    diff = stop.real - start.real
    puts sprintf("%2.2fx faster than base\n\n", base / diff)

    print "3/ Raw SQL with transaction: "
    start = Time.now.to_f
    ActiveRecord::Base.transaction{raw_sql}
    stop = Time.now.to_f
    diff = stop.real - start.real
    puts sprintf("%2.2fx faster than base\n\n", base / diff)

    print "4/ Single mass insert: "
    start = Time.now.to_f
    mass_insert
    stop = Time.now.to_f
    diff = stop.real - start.real
    puts sprintf("%2.2fx faster than base\n\n", base / diff)

    printf "5/ ActiveRecord::Extensions mass insert: "
    start = Time.now.to_f
    activerecord_extensions_mass_insert
    stop = Time.now.to_f
    diff = stop.real - start.real
    puts sprintf("%2.2fx faster than base\n\n", base / diff)

    print "6/ ActiveRecord::Extensions mass insert without validations: "
    start = Time.now.to_f
    activerecord_extensions_mass_insert false
    stop = Time.now.to_f
    diff = stop.real - start.real
    puts sprintf("%2.2fx faster than base", base / diff)
  end
end

5 So sánh hiệu năng của các giải pháp (2)

12/14

Kết quả thử nghiệm với 50 bản ghi:

hungnh@hungnh-X555LPB:~/Desktop/demo2$ rake insert_user:test
Testing various insert methods for 50 inserts

*base* ActiveRecord without transaction: 5.805713891983032 seconds

1/ ActiveRecord with transaction:32.51x faster than base

2/ Raw SQL without transaction: 0.88x faster than base

3/ Raw SQL with transaction: 43.69x faster than base

4/ Single mass insert: 20.79x faster than base

5/ ActiveRecord::Extensions mass insert: 46.47x faster than base

6/ ActiveRecord::Extensions mass insert without validations: 37.37x faster than base

ActiveRecord::Extensions mass insert có tốc độ thực thi nhanh nhất, gấp 46.47 lần base

5 So sánh hiệu năng của các giải pháp (3)

13/14

Kết quả thử nghiệm với 100 bản ghi:

hungnh@hungnh-X555LPB:~/Desktop/demo2$ rake insert_user:test
Testing various insert methods for 100 inserts

*base* ActiveRecord without transaction: 13.513744354248047 seconds

1/ ActiveRecord with transaction:53.16x faster than base

2/ Raw SQL without transaction: 0.85x faster than base

3/ Raw SQL with transaction: 99.32x faster than base

4/ Single mass insert: 28.13x faster than base

5/ ActiveRecord::Extensions mass insert: 53.09x faster than base

6/ ActiveRecord::Extensions mass insert without validations: 76.46x faster than base

Raw SQL with transaction có tốc độ thực thi nhanh nhất, gấp 99.32 lần base

Tài liệu tham khảo

14/14

Một số giải pháp insert nhiều bản ghi vào CSDL

By Nguyễn Huy Hùng

Made with Slides.com

Một số giải pháp insert nhiều bản ghi vào CSDL

  • 1,009

More from Nguyễn Huy Hùng