减少创建时的数据库查询量

时间：2022-01-16 21:56:33

Currently I'm working on an application that has to parse a large XML file and use the data inside of it to create entities in the database. The code for creating the entities is the following part of the a controller call:

目前我正在研究一个必须解析大型XML文件并使用其中的数据在数据库中创建实体的应用程序。创建实体的代码是控制器调用的以下部分:

def create
    parser = EuroPassVacancy.new params[:vacancy][:file].tempfile.path
    ids = parser.collect_vacancy_ids
    ids.each do |vacancy_id|
      country = Country.find_or_create_by_code(:code => parser.country_code(vacancy_id), :name => parser.country_code(vacancy_id))
      company = Company.find_or_create_by_name(:name => parser.employer_name(vacancy_id), :address => parser.employer_address(vacancy_id),
                                                  :contact => parser.employer_contact(vacancy_id), :phone => parser.employer_phone(vacancy_id),
                                                  :email => parser.employer_email(vacancy_id), :fax => parser.employer_fax(vacancy_id),
                                                  :country => country)
      Vacancy.create(:pes_id => parser.pes_id(vacancy_id), :title => parser.title(vacancy_id), :description => parser.description(vacancy_id),
                     :country => country, :region_code => parser.region_code(vacancy_id), :company_id => company, :isco_code => parser.isco_code(vacancy_id),
                     :nace_code => parser.nace_code(vacancy_id))
    end
    redirect_to :action => :index
  end

It's a pretty heavy loop, but i'm reasonably confident that the parsing is going ok. What currently bothers me is the amount of queries beeing generated for each create statement. Below is a small copy of 2 insert statement of Vacancies:

这是一个非常沉重的循环,但我有理由相信解析正常。目前困扰我的是为每个创建语句生成的查询量。以下是2份空缺职位空缺说明的小副本:

AREL (0.0ms)  INSERT INTO `vacancies` (`country_id`, `pes_id`, `title`, `region_code`, `isco_code`, `created_at`, `updated_at`, `description`, `company_id`, `nace_code`) VALUES (9, 50, 'Konstruktér strojní, Strojírenští technici projektanti, konstruktéři', 'RL041', 3118, '2011-05-25 13:19:16', '2011-05-25 13:19:16', 'ÚSO - strojní. Konstrukce nástrojů na tváření a lisování kovů, forem na lisování plastů. Znalost tech. a právních norem, ISO 9001, 14001 souvisejících s výrobním programem v rozsahu potřebném pro vývoj produktu a konstrukci výrobních prostředků - lisování a tváření kovů, plastů, montážních přípravků, PC - konstruk�ní SW - CAD, 3-D (Solid Edge výhodou), OFFICE Outlook, NJ/AJ uživatelská úroveň, ŘP sk. B.', 1, 0)
  SQL (31.2ms)  COMMIT
  Country Load (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  SQL (0.0ms)  BEGIN
  AREL (0.0ms)  INSERT INTO `countries` (`updated_at`, `name`, `code`, `created_at`) VALUES ('2011-05-25 13:19:18', 'cy', 'cy', '2011-05-25 13:19:18')
  SQL (63.0ms)  COMMIT
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'ΚΛΕΙΤΟΣ ΓΕΩΡΓΙΟΥ & ΥΙΟΣ ΛΙΜΙΤΕΔ' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'ΚΛΕΙΤΟΣ ΓΕΩΡΓΙΟΥ & ΥΙΟΣ ΛΙΜΙΤΕΔ' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'ΚΛΕΙΤΟΣ ΓΕΩΡΓΙΟΥ & ΥΙΟΣ ΛΙΜΙΤΕΔ') LIMIT 1
  SQL (0.0ms)  ROLLBACK
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  ROLLBACK
  Country Load (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'ΠΑ�ΙΚΚΟΣ ΠΙΣΣΟΥΡΙΟΣ ΚΑΙ ΑΔΕΛΦΟΣ ΚΟ ΛΤΔ' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (15.6ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'ΠΑ�ΙΚΚΟΣ ΠΙΣΣΟΥΡΙΟΣ ΚΑΙ ΑΔΕΛΦΟΣ ΚΟ ΛΤΔ' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'ΠΑ�ΙΚΚΟΣ ΠΙΣΣΟΥΡΙΟΣ ΚΑΙ ΑΔΕΛΦΟΣ ΚΟ ΛΤΔ') LIMIT 1
  SQL (0.0ms)  ROLLBACK
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  ROLLBACK
  CACHE (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'THE DELHI PALACE INDIAN RESTAURANT LTD' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'THE DELHI PALACE INDIAN RESTAURANT LTD' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'THE DELHI PALACE INDIAN RESTAURANT LTD') LIMIT 1
  SQL (0.0ms)  ROLLBACK
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  ROLLBACK
  CACHE (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'EURES ADVISOR , STELLA PAPADOPOULOU APPIOU' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'EURES ADVISOR , STELLA PAPADOPOULOU APPIOU' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'EURES ADVISOR , STELLA PAPADOPOULOU APPIOU') LIMIT 1
  AREL (0.0ms)  INSERT INTO `companies` (`email`, `phone`, `country_id`, `created_at`, `address`, `contact`, `fax`, `updated_at`, `name`) VALUES ('sappiou@dl.mlsi.gov.cy', '00357 22 403000', 10, '2011-05-25 13:20:13', 'MOUSEIOU STREET 3 1102 , ΛΕΥΚΩΣΙΑ', 'STELLA PAPADOPOULOU APPIOU', '00357 22 873170/1', '2011-05-25 13:20:13', 'EURES ADVISOR , STELLA PAPADOPOULOU APPIOU')
  SQL (31.2ms)  COMMIT
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  ROLLBACK
  Country Load (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'EURES ASSISTANT , NORA LANDA' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'EURES ASSISTANT , NORA LANDA' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'EURES ASSISTANT , NORA LANDA') LIMIT 1
  AREL (0.0ms)  INSERT INTO `companies` (`email`, `phone`, `country_id`, `created_at`, `address`, `contact`, `fax`, `updated_at`, `name`) VALUES ('nlanda@dl.mlsi.gov.cy', '00357 25 827327', 10, '2011-05-25 13:20:29', '67 FRANKLIN ROOSEVELT AV 3011 , ΛΕΜΕΣΟΣ', 'NOT AVAILABLE', '00357 25 306563', '2011-05-25 13:20:29', 'EURES ASSISTANT , NORA LANDA')
  SQL (31.2ms)  COMMIT
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  ROLLBACK
  Country Load (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'ΕΛΕ�Α ΤΖΙΟΒΑ�Η' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'ΕΛΕ�Α ΤΖΙΟΒΑ�Η' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'ΕΛΕ�Α ΤΖΙΟΒΑ�Η') LIMIT 1
  AREL (0.0ms)  INSERT INTO `companies` (`email`, `phone`, `country_id`, `created_at`, `address`, `contact`, `fax`, `updated_at`, `name`) VALUES ('georgiam85@hotmail.com', '0035723833780', 10, '2011-05-25 13:20:45', 'ΔΑΦ�ΗΣ 11-13 5314 AMMOHOSTOS', 'MRS GEORGIA MASIA', '0035723833177', '2011-05-25 13:20:45', 'ΕΛΕ�Α ΤΖΙΟΒΑ�Η')
  SQL (31.2ms)  COMMIT
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  ROLLBACK
  Country Load (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'cy' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'EURES ADVISOR , MINAS HADJICONSTANTI' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'EURES ADVISOR , MINAS HADJICONSTANTI' AND `companies`.`country_id` = 10 AND (`companies`.`name` = BINARY 'EURES ADVISOR , MINAS HADJICONSTANTI') LIMIT 1
  AREL (0.0ms)  INSERT INTO `companies` (`email`, `phone`, `country_id`, `created_at`, `address`, `contact`, `fax`, `updated_at`, `name`) VALUES ('mhadjiconstanti@dl.mlsi.gov.cy', '00357 24 805328', 10, '2011-05-25 13:21:01', 'P.O.BOX 40136 6301 , ΛΑΡ�ΑΚΑ', 'MINAS HADJICONSTANTI', '00357 24 304532', '2011-05-25 13:21:01', 'EURES ADVISOR , MINAS HADJICONSTANTI')
  SQL (62.4ms)  COMMIT
  SQL (0.0ms)  BEGIN
  SQL (1.0ms)  ROLLBACK
  Country Load (0.0ms)  SELECT `countries`.* FROM `countries` WHERE `countries`.`code` = 'it' LIMIT 1
  Company Load (0.0ms)  SELECT `companies`.* FROM `companies` WHERE `companies`.`name` = 'SAILER ANDREAS' LIMIT 1
  SQL (0.0ms)  BEGIN
  SQL (0.0ms)  SELECT 1 FROM `companies` WHERE `companies`.`name` = 'SAILER ANDREAS' AND `companies`.`country_id` = 8 AND (`companies`.`name` = BINARY 'SAILER ANDREAS') LIMIT 1
  AREL (0.0ms)  INSERT INTO `companies` (`email`, `phone`, `country_id`, `created_at`, `address`, `contact`, `fax`, `updated_at`, `name`) VALUES ('info@asailer.it', '+39-0471-932632', 8, '2011-05-25 13:21:18', 'A.Duerer-Str. 20 - Via Duerer 20 39100-Bozen - Bolzano', 'Giulia Morello', '+39-0471-932691', '2011-05-25 13:21:18', 'SAILER ANDREAS')
  SQL (30.0ms)  COMMIT
  SQL (0.0ms)  BEGIN
  AREL (0.0ms)  INSERT INTO `vacancies` (`country_id`, `pes_id`, `title`, `region_code`, `isco_code`, `created_at`, `updated_at`, `description`, `company_id`, `nace_code`) VALUES (8, 64, 'ricerca di ingegnere e architetto per studio di ingegneria', 'R3311', 4190, '2011-05-25 13:21:24', '2011-05-25 13:21:24', 'Studio di ingegneria cerca, anche per subito, un ingegnere e un architetto, per la progettazione architettonica e strutturale, stima e contabilità di opere, il tutto nel settore delle costruzioni civili e delle opere speciali . E\' richiesta ottima conoscenza di autocad, pacchetto office e programmi di strutture e contabilità. Sede di lavoro: Bolzano centro: si chiede gentilmente che venga inviato un curriculum professionale con foto allegata.', 1, 74)
  SQL (62.4ms)  COMMIT

There seems to be ALOT more select queries for the countries & companies going on than I'd like to have. I was wondering if an optimalization can be done about the code to reduce these queries.

对于正在进行的国家和公司而言,似乎有比我想要的更多选择查询。我想知道是否可以对代码进行优化以减少这些查询。

1 个解决方案

#1

3  

The simplest way to crack this is to cache the lookups or creations you perform. For instance, you're selecting the same country over and over again which is, as you've identified, wasteful.

解决此问题的最简单方法是缓存您执行的查找或创建。例如,你一遍又一遍地选择同一个国家,正如你所知,这是浪费。

You can do this by defining a helper method:

您可以通过定义辅助方法来完成此操作:

class Country < ActiveRecord::Base
  def self.resolve_country(options)
    @resolved_countries ||= { }

    @resolved_countries[options[:code]] ||= Country.find_or_create_by_code(options)
  end
end

This will load your country only once and if this pattern is applied to other models it should significantly speed up your loading.

这将仅为您的国家/地区加载一次,如果此模式应用于其他模型,则应显着加快您的加载速度。

One thing to keep in mind is when you may need to reset these caches for testing purposes. Adding a method to do this is always a good idea, and isn't hard:

要记住的一件事是,您可能需要重置这些缓存以进行测试。添加一个方法来做这个总是一个好主意,并不难:

def self.reset_cache!
  @resolved_countries = nil
end

This can be put in your setup method for your model's unit tests.

这可以放在模型单元测试的设置方法中。

#1

3  

The simplest way to crack this is to cache the lookups or creations you perform. For instance, you're selecting the same country over and over again which is, as you've identified, wasteful.

解决此问题的最简单方法是缓存您执行的查找或创建。例如,你一遍又一遍地选择同一个国家,正如你所知,这是浪费。

You can do this by defining a helper method:

您可以通过定义辅助方法来完成此操作:

class Country < ActiveRecord::Base
  def self.resolve_country(options)
    @resolved_countries ||= { }

    @resolved_countries[options[:code]] ||= Country.find_or_create_by_code(options)
  end
end

This will load your country only once and if this pattern is applied to other models it should significantly speed up your loading.

这将仅为您的国家/地区加载一次,如果此模式应用于其他模型,则应显着加快您的加载速度。

One thing to keep in mind is when you may need to reset these caches for testing purposes. Adding a method to do this is always a good idea, and isn't hard:

要记住的一件事是,您可能需要重置这些缓存以进行测试。添加一个方法来做这个总是一个好主意,并不难:

def self.reset_cache!
  @resolved_countries = nil
end

This can be put in your setup method for your model's unit tests.

这可以放在模型单元测试的设置方法中。

标签：

相关文章