The previous article Make your Ruby on Rails app 80x faster with SQL shows how SQL knowledge can help to optimize your Rails application performance. We discussed it within Belarus User Group community. Not everyone agreed with the point and didn’t find SQL knowledge a good asset for investment. That’s something expected. There is no revelation here. But that meeting had something that everyone was impressed with. We experimented and found out how ChatGPT is good with code optimization. We saw how it transforms Ruby code into performant SQL. The results were great. Check that out within this article.
ChatGPT request to transform Ruby code into SQL
As before, we are going to use this experimental repository.
We took the following code snippet, the slowest and original version, and asked to improve its performance by rewriting it into SQL:
# the original code projects_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 And we got the following response from 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; And these are the performance measurement results:
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 We also checked that the result of the query service is the same for both calls. That means there are no bugs in the produced SQL by the AI.
The ChatGPT result is the fastest one. At the same time, no business logic was defective. That’s a very good result!
Should one use ChatGPT to optimize code performance or not
While the AI results are good, it’s still doubtful who and how to use the tool. Not seasoned developers might don’t understand where the bottleneck is. They would struggle with finding a correct question for ChatGPT. They would not understand how to fix the generated code if it has bugs. Some time later I tried to repeat the experiment and know the machine produced a very different SQL that has bugs inside:
-- 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; While the AI results are good, it’s still doubtful who and how to use the tool. Not seasoned developers might don’t understand where the bottleneck is. They would struggle with finding a correct question for ChatGPT. They would not understand how to fix the generated code if it has bugs. Later, I tried to repeat the experiment. This time the machine produced a very different 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 However, it’s still faster than the original rewrite to SQL.
Conclusion
ChatGPT is very good at code refactoring and transforming Ruby code into SQL. Even though the results are impressive it still needs an expert communicating with the tool to form a correct question, check the produced results, and fix minor issues. The results can be a good start in code refactoring and optimization.
That was a great meeting. We had a memorable time together. Thanks to everyone who participated. I am looking forward to our weekly calls and I invite everyone to join the community.
Happy coding!
Optimize Rails app performance with ChatGPT was originally published by Andrei Kaleshka at Build Ruby On Rails applications with us on June 07, 2023.
Top comments (0)