SQL Performance Issue : Timing Out

Question

Following SQL query is taking more than 5:19 minutes to execute in Production. This SQL is dynamically generated from server side C# code and takes long time to execute for few clients. Due to long time in production Web service is timing out. Can you please optimize this SQL to improve performance ?

Here is the dynamically generated query -

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

This isn't a query. These are four queries.

The date comparisons look dangerous. They only work with certain language settings. The code generating the queries should be fixed.

As to the second query: whether the index IX_TepmBase_Audit_Date will be used, depends on whether the DBMS regards the date criteria limiting enough. I suppose that most records match the condition, so let's hope the DBMS doesn't use the index. Maybe an index on Action would help, as we are looking for all actions starting with a particular string.

In your third query you partition by t.id. Shouldn't the id be the table's unique column; the primary key maybe? A partition doesn't seem to make sense, and Rank would always be 1. Then the query contains a non-correlated NOT EXISTS clause. Either the condition is true or false for all records. That is very likely a mistake.

For the fourth query to run fast, you'd want an index on #TepmBaseExtend(id). And a further index for the derived table. Maybe on #TepmBaseExtend(Business_Date, Market_Value, Fund_Unique_Identifier). Not knowing your data, I am not sure which index exactly, but that one may work.