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
