none
SQL Server - Transact SQL - Optimization of query (High Priority) RRS feed

  • Question

  • Hi Everyone,

    Can you please provide the optimized query for the below one as this is consuming lot of time in one of our SQL jobs in production sometimes it takes less time as well.. Appreciated for your help. Does with (No lock) helps here??


    SELECT EmWith.Number, 
                         EmWith.Property, 
                         (EmWith.this2months + EmWith.last2months) as Total, 
                         EBal.Today ,
                         EmWith.Modified
    FROM            CA_MiscDB.dbo.ERASE_MISC EmWith WITH (NOLOCK) INNER JOIN 
    CA_MainDB.dbo.ERASE_BALANCE EBal WITH (NOLOCK) ON emWith.Number = EBal.Number INNER JOIN 
    CA_AccountDB.dbo.Properties [Policy_Open_Date] WITH (NOLOCK) ON EBal.Number = [Policy_Open_Date].ID 
    WHERE EmWith.Property = '@QUERY[Withdrawal_Profile, SELECT property, property + ' - ' + description FROM CA_MiscDB.dbo.PropertyDictionary (NOLOCK) ORDER BY Description]'
    AND [Policy_Open_Date].Property = 'Open_Date' 
    AND DateDiff(day, (case when (isdate([Policy_Open_Date].Value)=1) then convert(datetime,[Policy_Open_Date].Value,120) else null end), EmWith.Modified) < @DaysBack
    AND EmWith.this2months + EmWith.last2months >= EBal.Today * @Percentage * .01
    AND                                   datediff(d,getdate(),EmWith.modified)>=-@Activity_Since
    ORDER BY EmWith.ThisMonth

    Tuesday, July 14, 2020 6:51 PM