看看ChatGPT在代码重构或性能优化方面的表现如何。

前一篇文章《使用 SQL 使你的 Ruby on Rails 应用程序快 80 倍》展示了 SQL 知识如何帮助优化 Rails 应用程序的性能。我们在白俄罗斯用户组社区中讨论了这个问题。并不是每个人都同意这一观点，也不认为 SQL 知识是一项很好的投资。这是可以预料到的。但是那次会议有一些让每个人都印象深刻的事情。我们进行了实验，发现 ChatGPT 在代码优化方面非常出色。我们看到它如何将 Ruby 代码转换为高性能的 SQL。结果非常好。请在本文中查看。

ChatGPT 请求将 Ruby 代码转换为 SQL。

与以前一样，我们将使用这个实验性仓库。

我们拿了以下这段代码片段——最慢和最原始的版本，并要求通过重写为SQL来提高其性能：

# the original codeprojects_full = []
projects_empty = []
Service.where(user: user, status: "approved", active: true).order(category_id: :asc).each do |service|
  ratings_average = 0
  ratings_count = 0
  ratings_total = 0
  Rating.where(reviewee: user).each do |rating|
    project = Project.find(rating.project_id)
    if project.category_id == service.category_id
      ratings_count += 1
      ratings_total += rating.rating
    end
  end
  ratings_average = (ratings_total / ratings_count.to_f).round(1).to_s if ratings_count != 0 && ratings_total != 0
  completed_projects_count = Project.where(vendor: user, status: "Complete", category_id: service.category_id).size
  service_hash = {
    category_id: service.category_id,
    category_name: Category.find(Category.find(service.category_id).parent_id).name,
    subcategory_name: Category.find(service.category_id).name,
    completed_projects_count: completed_projects_count,
    ratings_count: ratings_count,
    ratings_average: ratings_average,
  }
  resolved_hash = service_hash.transform_keys{ |k| k.to_s.camelize(:lower) }
  if completed_projects_count > 0
    projects_full.push(resolved_hash)
  else
    projects_empty.push(resolved_hash)
  end
end
projects_full.sort_by!{ |k| -k["completedProjectsCount"] }
projects_full.push(*projects_empty)projects_full

我们从ChatGPT获得了以下回应：

SELECT
  c1.id AS category_id,
  c2.name AS category_name,
  c1.name AS subcategory_name,
  COUNT(DISTINCT p.id) FILTER (WHERE p.status = 'Complete') AS completed_projects_count,
  COUNT(r.id) AS ratings_count,
  COALESCE(AVG(r.rating), 0) AS ratings_average
FROM
  services s
  JOIN categories c1 ON s.category_id = c1.id
  JOIN categories c2 ON c1.parent_id = c2.id
  LEFT JOIN projects p ON s.user_id = p.vendor_id AND p.category_id = c1.id
  LEFT JOIN ratings r ON s.user_id = r.reviewee_id AND p.id = r.project_id
WHERE
  s.user_id = :user_id AND s.status = 'approved' AND s.active = true
GROUP BY
  c1.id, c2.name, c1.name
ORDER BY
  completed_projects_count DESC, c1.id ASC;

这些是绩效测量结果：

Warming up --------------------------------------
                ruby     1.000  i/100ms
                 sql    54.000  i/100ms
             sql_gpt    59.000  i/100ms
Calculating -------------------------------------
                ruby      6.846  (±14.6%) i/s -     34.000  in   5.000338s
                 sql    540.320  (± 8.5%) i/s -      2.700k in   5.038711s
             sql_gpt    609.335  (± 6.9%) i/s -      3.068k in   5.059364s
Comparison:
             sql_gpt:      609.3 i/s
                 sql:      540.3 i/s - same-ish: difference falls within error
                ruby:        6.8 i/s - 89.01x  slower

我们还检查了查询服务的结果两次调用是相同的。这意味着AI生成的SQL没有任何错误。

ChatGPT的结果是最快的。同时，没有任何业务逻辑错误。这是一个非常好的结果！

是否应该使用ChatGPT来优化代码性能？

虽然人工智能的结果很好，但是目前还有疑问：如何使用这个工具以及使用者是谁。非经验丰富的开发者可能不知道瓶颈在哪里，需要在寻找正确的 ChatGPT 问题方面花费大量时间。如果生成的代码存在错误，他们也不会知道如何修复。后来我试图重复实验，发现机器生成了一个带有错误的完全不同的 SQL。

-- Get average ratings and count for each service
WITH rating_summary AS (
  SELECT p.category_id, COUNT(r.id) AS ratings_count, COALESCE(AVG(r.rating), 0) AS ratings_average
  FROM ratings r
  INNER JOIN projects p ON p.id = r.project_id
  WHERE r.reviewee = <user_id>
  GROUP BY p.category_id
),
-- Get completed projects count for each service
completed_projects AS (
  SELECT category_id, COUNT(*) AS completed_projects_count
  FROM projects
  WHERE vendor = <user_id> AND status = 'Complete'
  GROUP BY category_id
)
-- Combine the results
SELECT
  s.category_id,
  c.name AS category_name,
  p.name AS subcategory_name,
  COALESCE(cp.completed_projects_count, 0) AS completed_projects_count,
  COALESCE(rs.ratings_count, 0) AS ratings_count,
  ROUND(COALESCE(rs.ratings_average, 0), 1) AS ratings_average
FROM services s
JOIN categories c ON c.id = s.category_id
JOIN categories p ON p.id = c.parent_id
LEFT JOIN rating_summary rs ON rs.category_id = s.category_id
LEFT JOIN completed_projects cp ON cp.category_id = s.category_id
WHERE s.user = <user_id> AND s.status = 'approved' AND s.active = true
ORDER BY completed_projects_count DESC;

虽然 AI 的结果很好，但是还不确定谁以及如何使用这个工具。不经验的开发者可能不理解瓶颈在哪里。他们可能会为 ChatGPT 找到正确的问题而苦苦挣扎。如果生成的代码有 bug，他们也不会知道如何修复它。后来我尝试重复实验。这一次机器生成了一个截然不同的 SQL：

Warming up --------------------------------------
                ruby     1.000  i/100ms
                 sql    52.000  i/100ms
             sql_gpt    40.000  i/100ms
         sql_gpt_new    38.000  i/100ms
Calculating -------------------------------------
                ruby      5.754  (±17.4%) i/s -     28.000  in   5.039290s
                 sql    433.067  (±20.3%) i/s -      2.080k in   5.029710s
             sql_gpt    561.018  (±14.4%) i/s -      2.760k in   5.044415s
         sql_gpt_new    514.242  (±19.4%) i/s -      2.432k in   5.013023s
Comparison:
             sql_gpt:      561.0 i/s
         sql_gpt_new:      514.2 i/s - same-ish: difference falls within error
                 sql:      433.1 i/s - same-ish: difference falls within error
                ruby:        5.8 i/s - 97.50x  slower

然而，仍比原来的SQL转写快。