I'm working on SQL code that basicly runs a complex SELECT statement with subqueries to report datastatus as of a given date, after verifying parameters. The code is designed to accept NULL values in all the parameters.
When I run the code through query analyzer using variables (all set to NULL), the process completes in under 30 seconds.
When I run the exact same statement in a stored procedure, replacing variables with parameters that default to NULL, the process runs for ten minutes or more without completing.
Here is the FROM clause:
inner join Account DealerAccount on Contract.ContractSubmitterAccountID = DealerAccount.AccountID
inner join Account HolderAccount on Contract.ContractHolderAccountID = HolderAccount.AccountID
inner join Claim on Contract.ContractID = Claim.ContractID
inner join ClaimAuth on Claim.ClaimID = ClaimAuth.ClaimID
inner join (select ClaimID, max(ClaimAuthSeq) ClaimAuthSeq from ClaimAuth ClaimAuth2 where ClaimAuthDt < @iDateTo or ClaimAuthDt is null Group by ClaimID) MaxClaims
on ClaimAuth.ClaimID = MaxClaims.ClaimID
and ClaimAuth.ClaimAuthSeq = MaxClaims.ClaimAuthSeq
left outer join (select ClaimID, Account.AccountID PayeeAccountID,
Account.AccountNo PayeeAccountNo, Account.AccountName PayeeAccountName, sum(Payment.Amount) PaidAmount
from ClaimPaysheet inner join Payment on ClaimPaysheet.PaymentID = Payment.PaymentID inner join AccountLedger on Payment.AccountLedgerID = AccountLedger.AccountLedgerID
inner join Account on Payment.PayeeAccountID = Account.AccountID
where AccountLedger.BusinessPeriodDate <= @iDateTo
Group by ClaimID, Account.AccountID, Account.AccountNo, Account.AccountName) TotalPayments
on Claim.ClaimID = TotalPayments.ClaimID
left outer join Account CarrierAccount on Contract.CarrierAccountID = CarrierAccount.AccountID
Where Claim.ClaimStatusID = 'OPEN' and ClaimAuth.Amount IS NOT NULL and datediff(dd, Claim.ReceivedDate, @iDateTo) <= @iDaysPending and isnull(TotalPayments.PaidAmount, 0) < ClaimAuth.Amount and Contract.EffectiveDt between @iFromSaleDate and @iToSaleDate
have you tried enabling generation of execution plan with and without nulls for your parameters? this is just to eliminate the possibility of generating different plans for different set of parms. if the plans are different, try to put (index=index_name) and see if this makes any difference. it is odd, and it surely sounds very much like a healthcare related. is this your business?