Slow Stored Procedure OK in Query Analizer 1

[SJB01]

I have a stored procedure that unions 3 select statements and is passed a couple of variables to filter the queries.

When I run the stored Proc it takes over a minute. If I cut and paste the exact SQL in to Query Analizer it runs in under 2 seconds.

I tried adding WITH RECOMPILE to the SP which seemed to fix the problem but now it is back

Any suggestions

 

[tlbroadbent]

Please post the query. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SJB01]

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

 

[tlbroadbent]

I have a few suggestions.
[ul][li] Use DBCC DBREINDEX to defragment indexes after large updates.
[li] Make sure the tables are indexed on columns used in WHERE clauses or JOIN criteria. The following indexes will be critical to performance.
[tt]
Table Columns
[tblMC-CashTranAdj] portcode, Cashaccountcode, ValuationDate
[vwtblMC-CashTranOpClBal] portcode, cashsecsymbol, AsOfDate, BALANCEDATE[tt]
[li]Use UNION ALL instead of UNION. This will inhibit the elimination of duplicate rows in the final result and will eliminate the need to SORT the result.
[li]Eliminate the use of DISTINCT in the select clause unless absolutely required to eliminate duplicate rows.
[li]Change the NOT sub-queries to use NOT EXISTS as in the following example.

AND Not Exists
(SELECT *
FROM [vwtblMC-CashTranOpClBal]
WHERE portcode = OLD.portcode
AND cashsecsymbol = OLD.cashsecsymbol
AND AsOfDate = @ValuationDate
AND BALANCEDATE = @ValuationStart)[/ul] If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SJB01]

All good suggestions many thanks.

 

[sguslan]

Terry,
Many stars for great insights .. ________________________________________________________________________________
If you do not like change, get out of the IT business...