By removing and Adding the WITH RECOMPILE the stored procedure now runs correctly. I also recalculate the statistics on the relevant tables. The DB is set to autoupdate the statistics. To maintain performance after a large upload to the database what should be the procedure.
I assume
1) Update Statistics
2) Recompile relevant Stored Procs
??
CREATE PROCEDURE usp_Checks_CashBalances
@Action as NvarChar(20),
@ValuationDate as datetime = 0,
@ValuationStart as DateTime = 0
WITH RECOMPILE
AS
SET NOCOUNT ON
Declare @RepFX as nvarchar(10)
SET @RepFX = 'pc'
--Differing Balances between valuations
SELECT DISTINCT NEW.portcode,
NEW.BalanceDate,
NEW.cashsecsymbol,
NEW.CashAccount,
NEW.CashAccountSymbol,
OLD.Local_Currency_Amount AS PreviousBalance,
NEW.Local_Currency_Amount AS NewBalance,
NEW.Local_Currency_Amount - OLD.Local_Currency_Amount AS [Difference],
IsNull((Select Sum (Local_Currency_Amount)
FROM [tblMC-CashTranAdj]
Where ValuationDate = @ValuationDate and PortCode = NEW.portcode and Cashaccountcode = NEW.cashsecsymbol),0) as CurrentAdjustments
FROM [vwtblMC-CashTranOpClBal] OLD INNER JOIN [vwtblMC-CashTranOpClBal] NEW ON NEW.portcode = OLD.portcode AND NEW.cashsecsymbol = OLD.cashsecsymbol
WHERE OLD.AsofDate = @ValuationStart - 1
AND OLD.BalanceDate = @ValuationStart - 1
AND NEW.AsofDate = @ValuationDate
AND NEW.BalanceDate = @ValuationStart
UNION
--Records where there no balance last Val but there is this val.
SELECT DISTINCT NEW.portcode,
NEW.BalanceDate,
NEW.cashsecsymbol,
NEW.CashAccount,
NEW.CashAccountSymbol,
0 AS PreviousBalance,
NEW.Local_Currency_Amount AS NewBalance,
NEW.Local_Currency_Amount AS [Difference],
IsNull((Select Sum (Local_Currency_Amount)
FROM [tblMC-CashTranAdj]
Where ValuationDate = @ValuationDate and PortCode = NEW.portcode and Cashaccountcode = NEW.cashsecsymbol),0) as CurrentAdjustments
FROM [vwtblMC-CashTranOpClBal] NEW
WHERE NEW.AsofDate = @ValuationDate
AND NEW.BalanceDate = @ValuationStart
AND portcode + cashsecsymbol Not IN ( SELECT portcode + cashsecsymbol
FROM [vwtblMC-CashTranOpClBal]
WHERE AsOfDate = @ValuationStart - 1
AND BALANCEDATE = @ValuationStart - 1)
UNION
--Records where there no balance Current Val but there is the previous val.
SELECT DISTINCT OLD.portcode,
@ValuationStart AS BalanceDate,
OLD.cashsecsymbol,
OLD.CashAccount,
OLD.CashAccountSymbol,
OLD.Local_Currency_Amount AS PreviousBalance,
0 AS NewBalance,
- OLD.Local_Currency_Amount AS [Difference],
IsNull((Select Sum (Local_Currency_Amount)
FROM [tblMC-CashTranAdj]
Where ValuationDate = @ValuationDate and PortCode = OLD.portcode and Cashaccountcode = OLD.cashsecsymbol),0) as CurrentAdjustments
FROM [vwtblMC-CashTranOpClBal] OLD
WHERE OLD.AsofDate = @ValuationStart - 1
AND OLD.BalanceDate = @ValuationStart - 1
AND portcode + cashsecsymbol Not IN ( SELECT portcode + cashsecsymbol
FROM [vwtblMC-CashTranOpClBal]
WHERE AsOfDate = @ValuationDate
AND BALANCEDATE = @ValuationStart )
GO
