Пользовательский алгоритм сопоставления Rails 3 с SQL-запросом (СЛОЖНО)

В настоящее время я работаю над приложением, которое сопоставляет пользователей на основе ответов на вопросы. Я реализовал свой алгоритм в обычных запросах RoR и ActiveRecord, но этос медленно, чтобы использовать его. Для сопоставления одного пользователя с 100 другими пользователями требуется

Completed 200 OK in 17741ms (Views: 106.1ms | ActiveRecord: 1078.6ms)

на моей локальной машине. Но все же ... Теперь я хочу реализовать это в сыром SQL, чтобы повысить производительность. Но я'У меня действительно возникают проблемы с тем, чтобы разобраться в SQL-запросах внутри SQL-запросов и тому подобном, плюс расчеты и т. д. Моя голова взорвется, и я неЯ даже не знаю, с чего начать.

Вот'Мой алгоритм:

def match(user)
  @a_score = (self.actual_score(user).to_f / self.possible_score(user).to_f) * 100
  @b_score = (user.actual_score(self).to_f / user.possible_score(self).to_f) * 100

  if self.common_questions(user) == []
    0.to_f
  else
    match = Math.sqrt(@a_score * @b_score) - (100 / self.common_questions(user).count)
    if match 
 Mexxer18 окт. 2012 г., 01:55
Держись яЯ собираюсь загрузить дамп базы данных с моими поддельными данными.
 willglynn18 окт. 2012 г., 02:15
Кроме того, какого масштаба вы пытаетесь достичь? Хотите ли вы сопоставить одного пользователя с 100 000 других пользователей?
 Mike Sherrill 'Cat Recall'18 окт. 2012 г., 01:41
Это'Другим легче помочь, если вы публикуете операторы SQL DDL и INSERT.
 willglynn18 окт. 2012 г., 02:13
Я могу'не могу говорить за @Catcall, но я бы предпочелSQLFiddle вместо дампа базы данных.
 Wizard of Ogz18 окт. 2012 г., 21:42
Здорово, рад, что у тебя все получилось!
 Mexxer18 окт. 2012 г., 02:22
ну, масштаб, вероятно, будет соответствовать одному пользователю с 10 000 пользователей. Если бы я мог повысить свою производительность, тогдаВсегда лучше, конечно. Нужны ли вам все ВСТАВКИ в SQLFiddle?
 Mexxer18 окт. 2012 г., 02:09
Вот! Это'Почти 11 Мб, но я хотел иметь реалистичный сценарий.uploaded.net/file/x28wn5kz

Ответы на вопрос(2)

Так вот's моя новая функция соответствия Я не могпока не помещаю все в один запрос, потому что SQLite неПоддерживает математические функции. Но как только я переключаюсь на MySQL, я помещаю все в один запрос. Все это уже дало мне ОГРОМНОЕ повышение производительности:

Completed 200 OK in 528ms (Views: 116.5ms | ActiveRecord: 214.0ms)

сопоставить одного пользователя со 100 другими пользователями. Достаточно хорошо! Я'Надо будет посмотреть, насколько хорошо это работает, как только я заполню свою базу 10k поддельных пользователей И дополнительные похвалы "Волшебник Огза " за указание на мой неэффективный код!

РЕДАКТИРОВАТЬ:

пробовал только с 1000 пользователей, от 10 до 100 UserQuestions каждый, и ...

Completed 200 OK in 104871ms (Views: 2146.0ms | ActiveRecord: 93780.5ms)

... мальчик сделал это долго! Мне придется что-то придумать, чтобы решить эту проблему.

def match(user)
if self.common_questions(user) == []
  0.to_f
else
  @a_score = UserQuestion.find_by_sql(["SELECT 100.0*as1.actual_score/ps1.possible_score AS match
      FROM (SELECT SUM(imp.value) AS actual_score 
      FROM user_questions AS uq1
      INNER JOIN importances imp ON imp.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id AND uq2.user_id = ?
      INNER JOIN accepted_answers aa ON aa.user_question_id =  uq1.id AND aa.answer_id = uq2.answer_id
      WHERE uq1.user_id = ?) AS as1, (SELECT SUM(value) AS possible_score 
      FROM user_questions AS uq1
      INNER JOIN importances ON importances.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq1.question_id = uq2.question_id AND uq2.user_id = ?
      WHERE uq1.user_id = ?) AS ps1",user.id, self.id, user.id, self.id]).collect(&:match).first.to_f
  @b_score = UserQuestion.find_by_sql(["SELECT 100.0*as1.actual_score/ps1.possible_score AS match
      FROM (SELECT SUM(imp.value) AS actual_score 
      FROM user_questions AS uq1
      INNER JOIN importances imp ON imp.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq2.question_id = uq1.question_id AND uq2.user_id = ?
      INNER JOIN accepted_answers aa ON aa.user_question_id =  uq1.id AND aa.answer_id = uq2.answer_id
      WHERE uq1.user_id = ?) AS as1, (SELECT SUM(value) AS possible_score 
      FROM user_questions AS uq1
      INNER JOIN importances ON importances.id = uq1.importance
      INNER JOIN user_questions uq2 ON uq1.question_id = uq2.question_id AND uq2.user_id = ?
      WHERE uq1.user_id = ?) AS ps1",self.id, user.id, self.id, user.id]).collect(&:match).first.to_f

  match = Math.sqrt(@a_score * @b_score) - (100 / self.common_questions(user).count)
  if match 

Я знаю, что вы думали о переходе на решение SQL, но есть некоторые существенные улучшения производительности, которые могут быть сделаны в вашем Ruby-коде, что может устранить необходимость использования SQL-кода, написанного вручную. При оптимизации вашего кода часто стоит использовать профилировщик, чтобы убедиться, что вы действительно знаете, какие части являются проблемой. В вашем примере я думаю, что некоторые большие улучшения могут быть сделаны путем удаления итеративного кода и запросов к базе данных, которые выполняются во время каждой итерации!

Кроме того, если вы используете последнюю версию ActiveRecord, вы можете генерировать запросы с подвыборками без необходимости кодирования любого SQL. Конечно, важно, чтобы у вас были правильные индексы, созданные для вашей базы данных.

Я делаю много предположений о ваших моделях и отношениях, основываясь на том, что я могу вывести из вашего кода. Если я'неправильно, дайте мне знать, и яЯ постараюсь внести некоторые коррективы соответственно.

def match(user)    
  if self.common_questions(user) == []
    0.to_f
  else
    # Move a_score and b_score calculation inside this conditional branch since it is otherwise not needed.
    @a_score = (self.actual_score(user).to_f / self.possible_score(user).to_f) * 100
    @b_score = (user.actual_score(self).to_f / user.possible_score(self).to_f) * 100
    match = Math.sqrt(@a_score * @b_score) - (100 / self.common_questions(user).count)
    if match  Answer.joins(:user_questions).where(:user_id => user.id)).
    sum(:value)
end

UserQuestion - это модель супер-соединения между пользователем, вопросом, ответом и важностью. Вот модельные отношения, относящиеся к коду (не включая has_many: через отношения, которые вы можете создать). Я думаю, что вы, вероятно, уже есть такие:

# User
has_many :user_questions

# UserQuestion
belongs_to :user
belongs_to :question
belongs_to :importance, :foreign_key => :importance  # Maybe rename the column `importance` to `importance_id`
belongs_to :answer

# Question
has_many :user_questions

# Importance
has_many :user_questions

# Answer
has_many :user_questions
 Mexxer18 окт. 2012 г., 14:42
таблица accept_answers состоит из:: id,: user_question_id и answer_id
 Wizard of Ogz18 окт. 2012 г., 16:29
Я не'Не понимаю, что accept_answers хранятся в отдельной таблице. Это немного изменит код для #actual_score. #Possible_score возвращает правильный результат?
 Wizard of Ogz18 окт. 2012 г., 16:32
Правильно ли работал #possible_score после изменения Question.joins () ... обратно на user.user_questions ...?
 Mexxer18 окт. 2012 г., 16:28
Ну, дело в том, что это тоже недайте мне правильные результаты.
 Mexxer18 окт. 2012 г., 19:41
я отредактировал мой вопрос с новым возможным SQL-оператором и ложным фактическим результатом SQL Вы были бы моим героем, если бы вы могли помочь мне получить эту работу!
 Mexxer18 окт. 2012 г., 16:38
Да только user.user_questions дал мне правильные результаты.
 Mexxer18 окт. 2012 г., 17:16
И вот что выдает user.user_questions: SELECT SUM (value) AS sum_id FROM "user_questions» ВНУТРЕННЕЕ СОЕДИНЕНИЕ "важностей» ON "важностей «»id "="user_questions ""значение» ГДЕ "user_que stions "."Идентификатор пользователя" = 1 И "user_questions ""question_id» IN (ВЫБЕРИТЕ question_id FROM "user_questions» ГДЕ "user_questions ""Идентификатор пользователя" = 101 ORDER BY user_q uestions.id DESC) I '
 Mexxer18 окт. 2012 г., 17:15
Причина, по которой Question.joins () неЭто происходит потому, что он выглядит, если user2 является автором Вопросов, на которые user1 ответил своими UserQuestions.
 Mexxer18 окт. 2012 г., 14:38
но я'Я уже очень благодарен за это! Я могу'Хотя я не совсем понимаю фактическую оценку. Я'мы пробовали это: где (: question_id => user.user_questions.select (: question_id),: answer_id => user.user_questions.select (: answer_id)) ... но нет работа. Мне нужно посмотреть, если пользователь2 дал ответ, который находится в принятых ответах от пользователя1.
 Mexxer18 окт. 2012 г., 14:36
сделать где (: question_id => Question.joins (: user_questions) .where (: user_id => user.id)) с возможной оценкой до: где (: question_id => user.user_questions.select (: question_id)) ... модель вопроса нет часть этого. Любой может создать вопрос, и любой может ответить на него. Таким образом, отвеченный вопрос_id хранится в пользовательском вопросе.
 Wizard of Ogz18 окт. 2012 г., 16:22
@mexxer Причина использования Question.joins () ... вместо user.user_questions заключается в том, что первый генерирует один запрос с использованием подвыбора. Последний будет генерировать 2 запроса и использовать IN вместо подвыбора. Вы можете проверить, какой из них более эффективен.
 Mexxer19 окт. 2012 г., 11:15
Как вы думаете, это может быть хорошей идеей, чтобы посмотреть на решение NoSQL для этого? Потому что я определенно смогу денормализовать все эти таблицы в одну таблицу user_questions. Но я'Я не уверен, что это даст мне гораздо большую производительность, если рельсы все еще будут выполнять все вычисления.

Ваш ответ на вопрос