如何在控制台的 activerecord-sqlserver-adapter 中调试 sql 查询

Question

如果有人有几个空闲时间(或几天(来帮助我优化几个电话并希望获得报酬(我可以提供每小时 150 美元(的帮助，我真的很想得到你的帮助。我越来越绝望:)

我有一些很慢的sql查询：

Panel Load (1075.7ms)  EXEC sp_executesql N'SELECT [panels].* FROM [panels] WHERE [panels].[agglo_code_id] = @0 AND [panels].[environment_id] = @1 AND [panels].[product_id] = @2 AND (NOT EXISTS(SELECT 1 FROM campaign_search_panels WHERE campaign_search_panels.panel_id = panels.panel_id AND campaign_search_panels.campaign_id = 32)) AND (NOT EXISTS(SELECT 1 FROM "AIDAAU_Avails" WHERE "AIDAAU_Avails"."PanelID" = panels.panel_uid AND "AIDAAU_Avails"."TillDate" >= ''08-21-2017'' AND "AIDAAU_Avails"."FromDate" <= ''09-03-2017''))', N'@0 int, @1 int, @2 int', @0 = 24, @1 = 14, @2 = 25  [["agglo_code_id", 24], ["environment_id", "14"], ["product_id", "25"]]

我试图弄清楚如何调试它，但我不能完全正确。我想对它进行解释，但是我无法通过sql客户端直接访问数据库，因为它被锁定到服务器的ip，所以我正在尝试通过服务器上的rails控制台进行操作。

我可以执行以下操作(不确定为什么它会运行两个查询(：

irb(main):049:0> ActiveRecord::Base.connection.execute('SELECT [panels].* FROM [panels] WHERE [panels].[agglo_code_id] = 24 AND [panels].[environment_id] = 14 AND [panels].[product_id] = 25 AND (NOT EXISTS(SELECT 1 FROM campaign_search_panels WHERE campaign_search_panels.panel_id = panels.panel_id AND campaign_search_panels.campaign_id = 32)) AND (NOT EXISTS(SELECT 1 FROM "AIDAAU_Avails" WHERE "AIDAAU_Avails"."PanelID" = panels.panel_uid AND "AIDAAU_Avails"."TillDate" >= ''08-21-2017'' AND "AIDAAU_Avails"."FromDate" <= ''09-03-2017''))')
(47.3ms)  SELECT [panels].* FROM [panels] WHERE [panels].[agglo_code_id] = 24 AND [panels].[environment_id] = 14 AND [panels].[product_id] = 25 AND (NOT EXISTS(SELECT 1 FROM campaign_search_panels WHERE campaign_search_panels.panel_id = panels.panel_id AND campaign_search_panels.campaign_id = 32)) AND (NOT EXISTS(SELECT 1 FROM "AIDAAU_Avails" WHERE "AIDAAU_Avails"."PanelID" = panels.panel_uid AND "AIDAAU_Avails"."TillDate" >= 08-21-2017 AND "AIDAAU_Avails"."FromDate" <= 09-03-2017))
(47.3ms)  SELECT [panels].* FROM [panels] WHERE [panels].[agglo_code_id] = 24 AND [panels].[environment_id] = 14 AND [panels].[product_id] = 25 AND (NOT EXISTS(SELECT 1 FROM campaign_search_panels WHERE campaign_search_panels.panel_id = panels.panel_id AND campaign_search_panels.campaign_id = 32)) AND (NOT EXISTS(SELECT 1 FROM "AIDAAU_Avails" WHERE "AIDAAU_Avails"."PanelID" = panels.panel_uid AND "AIDAAU_Avails"."TillDate" >= 08-21-2017 AND "AIDAAU_Avails"."FromDate" <= 09-03-2017))
=> 1143

它比上面的要快得多，但那是因为我替换了所有的标量变量还是为什么它快得多？有什么方法可以完全相同地运行查询吗？即：

query = <<-SQL 
EXEC sp_executesql N'SELECT [panels].* FROM [panels] WHERE [panels].[agglo_code_id] = @0 AND [panels].[environment_id] = @1 AND [panels].[product_id] = @2 AND (NOT EXISTS(SELECT 1 FROM campaign_search_panels WHERE campaign_search_panels.panel_id = panels.panel_id AND campaign_search_panels.campaign_id = 32)) AND (NOT EXISTS(SELECT 1 FROM "AIDAAU_Avails" WHERE "AIDAAU_Avails"."PanelID" = panels.panel_uid AND "AIDAAU_Avails"."TillDate" >= ''08-21-2017'' AND "AIDAAU_Avails"."FromDate" <= ''09-03-2017''))', N'@0 int, @1 int, @2 int', @0 = 24, @1 = 14, @2 = 25  [["agglo_code_id", 24], ["environment_id", "14"], ["product_id", "25"]]
SQL
ActiveRecord::Base.connection.execute(query)
ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near '["agglo_code_id", 24'.:

有什么想法可以改进吗？

Answer 1

如果没有执行计划，将很难诊断出确切的性能问题是什么。但是，仅通过查看您的SQL，我就会看到一个巨大的危险信号，这可能是您的性能问题。

SELECT
[panels].*
FROM [panels]
WHERE
[panels].[agglo_code_id] = @0
AND
[panels].[environment_id] = @1
AND
[panels].[product_id] = @2
AND
(
NOT EXISTS( SELECT 1
FROM campaign_search_panels
WHERE
campaign_search_panels.panel_id = panels.panel_id
AND
campaign_search_panels.campaign_id = 32)
)
AND
(
NOT EXISTS( SELECT 1
FROM AIDAAU_Avails
WHERE
AIDAAU_Avails.PanelID = panels.panel_uid
AND
AIDAAU_Avails.TillDate >= '08-21-2017'
AND
AIDAAU_Avails.FromDate <= '09-03-2017')
)

当我提取您的动态 SQL 并使其美观时，我发现了两件可能导致性能问题的事情。首先，您有一个SELECT *它将从表中获取每一列，无论您是否需要它。您可能会放慢自己的速度，因为您正在获取比实际需要的更多的数据。

第二件事，也是我巨大的危险信号，是你有两个运行SQL查询的NOT EXISTS子句。根据三个表之间的数据量，这可能是一项非常昂贵的操作。对于主查询返回的每条记录，您需要运行每个NOT EXISTS查询。这意味着如果主查询返回 100 行，则必须运行 200 个额外的查询才能满足 where 子句。

要解决此问题，您应该能够将这些NOT EXISTS替换为 两个LEFT JOIN.我可以猜测如何做到这一点，但是如果没有数据可以使用，我无法确定，也不想给你一些让事情变得更糟的东西。

为了让您了解性能差异，我有一个查询在做类似的事情。由于数据的大小，运行需要 36 小时。我用某种JOIN替换了子查询，并在不到一个小时的时间内运行了它。