SQL性能问题:时间出现

Question

SQL查询之后的

需要超过5:19分钟才能执行生产。该SQL是从服务器端C＃代码动态生成的，需要很长时间才能为少数客户端执行。由于生产长时间，网络服务正在计时。您可以优化此SQL以提高性能吗？

这是动态生成的查询 -

SELECT *
  INTO #TepmBase
FROM PFInputDemoV3.dbo.Position_Level_Data_Audit
WHERE PFA_Unique_Identifier = 'TEST_DRILLDOWN_ADVISER'
AND Business_Date BETWEEN '4/1/2017' AND '6/30/2017';
CREATE INDEX IX_TepmBase_ID ON #TepmBase (ID);
CREATE INDEX IX_TepmBase_Audit_Date ON #TepmBase (Audit_Date);
SELECT DISTINCT ID
  INTO #TempBaseDeleted
FROM #TepmBase
WHERE Audit_Date < '8/30/2017 2:09:22 PM'
AND Action LIKE 'Dele%';

SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
  INTO #TepmBaseExtend
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY Audit_id DESC) AS Rank
  FROM #TepmBase t
  WHERE Audit_Date < '8/30/2017 2:09:22 PM'
    AND NOT Action LIKE 'Dele%'
    AND ID NOT IN (SELECT ID FROM #TempBaseDeleted)
    AND NOT EXISTS (SELECT 1
      FROM #TepmBase
      WHERE Audit_Date BETWEEN '8/30/2017 2:09:22 PM' AND '8/30/2017 2:10:46 PM')) c
WHERE RANK = 1
  AND (ValidatedStatus = 1 OR ValidatedStatus = 3 OR ValidatedStatus = 5
       OR ValidatedStatus = 7 OR ValidatedStatus = 9 OR ValidatedStatus = 11
       OR ValidatedStatus = 13 OR ValidatedStatus = 15 OR ValidatedStatus = 17
       OR ValidatedStatus = 19 OR ValidatedStatus = 21 OR ValidatedStatus = 23
       OR ValidatedStatus = 25 OR ValidatedStatus = 27 OR ValidatedStatus = 29
       OR ValidatedStatus = 31 OR ValidatedStatus = 33 OR ValidatedStatus = 35
       OR ValidatedStatus = 37 OR ValidatedStatus = 39  OR ValidatedStatus = 41
       OR ValidatedStatus = 43 OR ValidatedStatus = 45  OR ValidatedStatus = 47
       OR ValidatedStatus = 49 OR ValidatedStatus = 51  OR ValidatedStatus = 53
       OR ValidatedStatus = 55 OR ValidatedStatus = 57  OR ValidatedStatus = 59
       OR ValidatedStatus = 61 OR ValidatedStatus = 63);

CREATE UNIQUE CLUSTERED INDEX IX_#TepmBaseExtend_RowNumber ON #TepmBaseExtend (RowNumber);
SELECT
  ID,
  PFA_Unique_Identifier,
  Fund_Unique_Identifier,
  Business_Date,
  Information_Date,
  Position_Unique_Identifier,
  Instrument_Group,
  Instrument_Category,
  Instrument_Type,
  Option_Instrument,
  Price,
  Local_Price,
  Quantity,
  LongShort,
  Market_Value,
  Notional_Value,
  Issuer_Counterparty,
  Issuer_Counterparty_Type,
  Issuer_Counterparty_Country,
  Issuer_Counterparty_LegalName,
  Issuer_CounterParty_Affiliate,
  Issuer_Counterparty_Affiliate_Other,
  Investment_Quality,
  Delta,
  Vega,
  DV01,
  IR_Sensitivity_Measure,
  IR_Sensitivity_Measure_Value,
  Strategy,
  Strategy_Name_Other,
  High_Freq_Trading_Indicator,
  Liquidation_Horizon_Days,
  Cleared_by_a_CCP,
  AssetClass_Other,
  Value,
  Exclude_Issuer_Counterparty,
  Exclude_Strategy,
  Exclude_Issuer_Counterparty_Country,
  Symbol,
  Instrument_Description,
  Region,
  Any_Securities_Borrowing_Lending,
  _Highlight
FROM (SELECT
  CAST(b.ID AS bigint) AS ID,
  PFA_Unique_Identifier,
  Fund_Unique_Identifier,
  Business_Date,
  Information_Date,
  Position_Unique_Identifier,
  Instrument_Group,
  Instrument_Category,
  Instrument_Type,
  Option_Instrument,
  Price,
  Local_Price,
  Quantity,
  LongShort,
  Market_Value,
  Notional_Value,
  Issuer_Counterparty,
  Issuer_Counterparty_Type,
  Issuer_Counterparty_Country,
  Issuer_Counterparty_LegalName,
  Issuer_CounterParty_Affiliate,
  Issuer_Counterparty_Affiliate_Other,
  Investment_Quality,
  Delta,
  Vega,
  DV01,
  IR_Sensitivity_Measure,
  IR_Sensitivity_Measure_Value,
  Strategy,
  Strategy_Name_Other,
  High_Freq_Trading_Indicator,
  Liquidation_Horizon_Days,
  Cleared_by_a_CCP,
  AssetClass_Other,
  Value,
  Exclude_Issuer_Counterparty,
  Exclude_Strategy,
  Exclude_Issuer_Counterparty_Country,
  Symbol,
  Instrument_Description,
  CASE
    WHEN ISNULL(Region, '@') = '@' THEN (SELECT TOP 1 PF_Region
      FROM Parameters.dbo.Country_Region_Mapping
      WHERE UPPER(Issuer_Counterparty_Country) = Country_Code)
    ELSE Region
  END AS Region,
  Any_Securities_Borrowing_Lending,
  CASE
    WHEN h.id IS NULL THEN 'N'
    ELSE 'Y'
  END AS _Highlight,
  ROW_NUMBER() OVER (ORDER BY rownumber) AS rn
FROM #TepmBaseExtend b
INNER JOIN (SELECT ID
  FROM #TepmBaseExtend
  WHERE (UPPER(Exclude_Issuer_Counterparty_Country) IN ('NO','N')
         OR Exclude_Issuer_Counterparty_Country IS NULL)
    AND Business_Date = '6/30/2017'
    AND Fund_Unique_Identifier IN
        (SELECT Fund_Unique_Identifier
         FROM PFInputDemoV3..Fund_AUM_Data
         WHERE PFA_Unique_Identifier = 'TEST_DRILLDOWN_ADVISER'
           AND Business_Date = '6/30/2017'
           AND UPPER(Fund_Type) = 'HEDGE')
           AND Fund_Unique_Identifier IN
               (SELECT Fund_Unique_Identifier
                FROM PFInputDemoV3..Fund_Level_Information
                WHERE PFA_Unique_Identifier = 'TEST_DRILLDOWN_ADVISER'
                  AND Business_Date = '6/30/2017')
                  AND Market_Value > 0
                  AND UPPER(Issuer_Counterparty_Country) IN
                      (SELECT DISTINCT Country_Code
                       FROM Parameters.dbo.Country_Region_Mapping
                       WHERE UPPER(PF_Region) = UPPER('Europe_EEA'))) h
  ON h.id = b.id) tmp
WHERE rn BETWEEN 0 AND 100;
DROP TABLE #TepmBase;
DROP TABLE #TepmBaseExtend;
DROP TABLE #TempBaseDeleted;

Answer 1

这不是查询。这些是四个查询。

日期比较看起来很危险。他们只能使用某些语言设置。生成查询的代码应修复。

关于第二个查询：是否使用索引IX_TepmBase_Audit_Date，取决于DBMS是否对日期标准限制是否足够限制。我想大多数记录与该条件匹配，因此希望DBMS不要使用索引。也许Action上的索引会有所帮助，因为我们正在寻找以特定字符串开头的所有操作。

在您的第三个查询中，您可以通过t.id进行分区。id是否应该是表的唯一列；也许是主要钥匙？一个分区似乎没有意义，Rank始终是1。然后查询包含一个无关的NOT EXISTS子句。对于所有记录，条件是真实或错误的。这很可能是一个错误。

要使第四个查询快速运行，您需要在#TepmBaseExtend(id)上进行索引。以及派生表的进一步索引。也许在#TepmBaseExtend(Business_Date, Market_Value, Fund_Unique_Identifier)上。不知道您的数据，我不确定哪个索引到哪个索引。