Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Question Unanswered: Stored Procedure Query Optimization - Query TimeOut error

    How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :

    CREATE PROCEDURE proc1
    @Franchise ObjectId
    , @dtmStart DATETIME
    , @dtmEnd DATETIME
    AS
    BEGIN


    SET NOCOUNT ON

    SELECT p.Product
    , c.Currency
    , c.Minor
    , a.ACDef
    , e.Event
    , t.Dec
    , count(1) "Count"
    , sum(Amount) "Total"
    FROM tb_Event t
    JOIN tb_Prod p
    ON ( t.ProdId = p.ProdId )
    JOIN tb_ACDef a
    ON ( t.ACDefId = a.ACDefId )
    JOIN tb_Curr c
    ON ( t.CurrId = c.CurrId )
    JOIN tb_Event e
    ON ( t.EventId = e.EventId )
    JOIN tb_Setl s
    ON ( s.BUId = t.BUId
    and s.SetlD = t.SetlD )
    WHERE Fran = @Franchise
    AND t.CDate >= @dtmStart
    AND t.CDate <= @dtmEnd
    AND s.Status = 1
    GROUP BY p.Product
    , c.Currency
    , c.Minor
    , a.ACDef
    , e.Event
    , t.Dec

    RETURN 1
    END



    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I am required by the DBA equivalent of the Hippocratic oath to ask you this question:

    Are the foreign keys in your tables indexed, and have you updated your database statistics?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    Yes , all the tables are indexed and it is using nested joins.The database statistics are also updated.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Do you have an index that has these fields (Fran,CDate)?
    You can do hair-splitting on anything, but fundamentals need to be covered. And CDate can be used with BETWEEN in the JOIN clause as well. And what's the meaning of COUNT(1)? Also, returning 1 means ERROR based on defacto standards, do RETURN (0) instead.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't see anything wrong in your SQL statement (except that count(1) thing. Just use count(*) instead), so again you should look at either indexing, hardware, or contention issues.

    Try running your statement in Query Analyzer with the "Show Execution Plan" query option on. That should show where the big time sink is.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just curious, but where did you get sp4? Microsoft's SQL page only shows sp3a, which is the latest service pack released to production. If that is a PSS pre-release, you probably want to report the problem to them. If it is not a PSS pre-release, I'd be rather nervous about it!

    -PatP

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SP4 beta is still being tested and is not considered a full-fledged release yet. The poster probably referred to Win2K SP4.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    --"and have you updated your database statistics?"

    can any one please explain what is meant by "database statistics"
    Cheers....

    baburajv

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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