Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Procedure performance

    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:
    FROM Contract
    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

    This is a new one on me. Any suggestions?

    blindman

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Was the first run of the procedure done with non-null parameters? I am wondering if add "with recompile" will help there.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Tried recompile. Tried statistics. No difference.

    I can probably solve this by breaking it down into parts, but it is still curious that the code would run order of magnitude slower as a procedure than through Query Analyzer.

    blindman

  4. #4
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72
    Yes, it is curious.

    Try making indexes for :

    Contract.ContractSubmitterAccountID
    Contract.ContractHolderAccountID
    Account.AccountID
    Claim.ContractID
    ClaimAuth.ClaimID
    Payment.PaymentID
    ClaimPaysheet.PaymentID

    ClaimID, Account.AccountID, Account.AccountNo, Account.AccountName

    Claim.ClaimStatusID

    Claim.ReceivedDate

    Contract.EffectiveDt

    and taking care to make first the joins for the tables with less data


    Hope it helps

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The tables are well indexed, and the logic runs in a reasonable length of time through Query Analyzer.

    blindman

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Gee blindman, what a construction! Isn't there a chance to let a small and efficient clause come out of it? Just kidding.

    Obviously, the Query Analyzer makes another execution plan, than you get in your compiled stored procedure. I wonder if that plan can be extracted somehow.

    I didn't understand your clause, but I wonder if there is a chance to force a given plan by putting some joins into a view or making some hints?!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you'd read my second post, I am breaking it into smaller parts, and I expect to get better performance out of it or at least be able to narrow down where the problem is.

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •