时间戳

首页 > 解决方案 > 改进低效查询

ruby-on-rails - 改进低效查询

问题描述

我遇到了一个问题，即我的应用程序超时，因为查询时间过长。我正在使用 Heroku 的免费层，所以我并没有完全优先考虑速度，因此我遇到了我在本地看不到的超时。我希望有人能看到我的查询有问题，这可以让我加快速度。

我的查询如下所示：

  def index
    # file = File.read('app\controllers\recipe.csv')
    clear_database
    file = get_file
    recipe_array = file.split("\n")
    dbUser = User.find_by(id: 999999999)
    recipe_array.each do |recipe|
      # I'm saving here becuase I need the ID later
      dbRecipe = dbUser.recipes.create

      recipe = recipe.split(",")
      url_index = recipe.length - 1
      img_url_index = recipe.length - 2
      recipe.each_with_index do |item, index|
        if index == 0
          dbRecipe.name = item.strip.downcase
          dbRecipe.save
        elsif index == url_index
          dbRecipe.url = item
          dbRecipe.save
        elsif index == img_url_index
          dbRecipe.img_url = item
          dbRecipe.save
        elsif index.odd?
          count = item
          food = recipe[index + 1]
          dbIngredient = Ingredient.new
          dbFood = Food.find_by_name(food)
          if dbFood.nil?
            dbFood = Food.new
            dbFood.name = food.strip.downcase
            dbFood.save
          end

          # populate ingredient
          dbIngredient.unit_type = item.split(" ").last
          dbIngredient.quantity = item.split(" ").first
          # I'm saving so much above because I need the id's
          dbIngredient.recipe_id = dbRecipe.id
          dbIngredient.food_id = dbFood.id
          dbIngredient.save
        end
      end
    end
  end

我的数据由存储在 CSV 文件中的食谱组成。大约有 300 行如下所示：

"Sirloin Steak with Blue Cheese Compound Butter,0.6 oz, Butter,2 count, Garlic Cloves,2 count, Green Onions,12 oz, Fingerling Potatoes,8 oz, Green Beans,12 oz, Sirloin Steaks,1 oz, Blue Cheese,https://homechef.imgix.net/https%3A%2F%2Fasset.homechef.com%2Fuploads%2Fmeal%2Fplated%2F2543%2F2543SirloinSteakwithBlueCheeseCompoundButterReshoot2__1_of_1_-b04048840f58000cef80b38fc3f77856-b04048840f58000cef80b38fc3f77856.jpg?ixlib=rails-1.1.0&w=425&auto=format&s=eeba60ce35bcee4938a11286cbea0203,https://www.homechef.com/meals/sirloin-steak-with-blue-cheese-compound-butter
Teriyaki Ginger-Glazed Salmon,1 Tbsp, Chopped Ginger,2 count, Garlic Cloves,2 count, Green Onions,8 oz, Carrot,2 count, Heads of Baby Bok Choy,1 count, Red Fresno Chile,2 oz, Teriyaki Glaze,12 oz, Salmon Fillets,https://homechef.imgix.net/https%3A%2F%2Fasset.homechef.com%2Fuploads%2Fmeal%2Fplated%2F3429%2F3429TeriyakiGinger-GlazedSalmonReshoot3__1_of_1_-73adcd6ad23cc72b28fdba85387fa18a-73adcd6ad23cc72b28fdba85387fa18a.jpg?ixlib=rails-1.1.0&w=425&auto=format&s=9e6b37380203ec5a58a5ddb906b5ae8b,https://www.homechef.com/meals/teriyaki-ginger-glazed-salmon
Al Pastor Pork Flautas,1 count, Shallot,1 count, Lime,3 oz, Pineapple Chunks,1 oz, Queso Fresco,12 oz, Ground Pork,1 tsp, Chipotle Seasoning,6 count, Small Flour Tortillas,0.5 oz, Baby Arugula,1 oz, Sour Cream,10 oz, Ground Beef,https://homechef.imgix.net/https%3A%2F%2Fasset.homechef.com%2Fuploads%2Fmeal%2Fplated%2F4290%2F4290AlPastorPorkFlautasFinal2__1_of_1_-4e7fe04ac157a463b4d93eb57e9b93f9-4e7fe04ac157a463b4d93eb57e9b93f9.jpg?ixlib=rails-1.1.0&w=425&auto=format&s=de2e2403d7261f2697567faf5f477359,https://www.homechef.com/meals/al-pastor-pork-flautas

标签： ruby-on-railsrubydatabasepostgresqlactiverecord

解决方案

您试图在单个 http 请求/响应周期内做太多事情。对于 CSV 中的每一行，您都在执行 sql INSERT(with dbUser.recipes.create)、an UPDATE(with dbRecipe.save) 和SELECT(with Food.find_by_name(food))。

即使您进行了一些优化，您确定 CSV 在您的应用程序的整个生命周期中只会有大约 300 行吗？即使答案是肯定的，一般来说，最好的做法是尽快响应用户操作，而不是让他们看着浏览器等待响应。

所以，我建议你重新考虑你的方法。如果单个操作必须执行大量 sql 命令，请考虑让任务异步运行的方法。这就是ActiveJob（https://edgeguides.rubyonrails.org/active_job_basics.html）和sidekiq（https://github.com/mperham/sidekiq/）等工具的设计目的。

例如，以这样一种方式设计您的应用程序：用户单击某个按钮以上传 CSV 并回复：“感谢您的提交，我们正在努力！”。用户可以随时回来检查正在处理的文件的状态或刷新屏幕。或者，您可以通过 AJAX 轮询或通过 websockets 进行双向通信来获得更复杂和自动化的状态检查。Railsy 的方法是使用ActionCable( https://guides.rubyonrails.org/action_cable_overview.html )。

推荐阅读