Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Question on Efficiency of Subquery

    I have table as follows


    tbl_tickets

    id - INT primary key
    ticket_id VARCHAR(80)
    start_time DATETIME
    end_time DATETIME

    I want to work out the average transaction time (end_time - start_time) only on distinct records

    This is the way I would do this:

    Code:
    SELECT AVG(datediff(ss, end_time, start_time))
    FROM( SELECT DISTINCT  ticket_id
                                      , start_time
                                      , end_time
              FROM tbl_tickets) x
    If the table had a million records the subquery would pull out a large chunk of data - isnt this inefficient? Is there any other way to do this more efficiently without redesigning the database?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ozzii View Post
    If the table had a million records the subquery would pull out a large chunk of data
    The number of distinct rows would depend on the data distribution, it might be large or small.

    Quote Originally Posted by ozzii View Post
    isnt this inefficient?
    Not if it is the most efficent way to compute what you need.

    Quote Originally Posted by ozzii View Post
    Is there any other way to do this more efficiently without redesigning the database?
    Probably, depending on what you need.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What constitutes a distinct record? You have a surrogate ID, so there should not be any duplicates. What is the natural key for your table?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by blindman View Post
    What constitutes a distinct record? You have a surrogate ID, so there should not be any duplicates. What is the natural key for your table?
    Natural key is id but i want to work out average on the ticket_id of which there can be duplicates.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    ... there can be duplicates.
    there was another guy who has a similar problem as yours -- duplicates

    see http://www.dbforums.com/microsoft-sq...nner-join.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then as Pat said, any inefficiencies are due to your database design, and the query you posted is going to be about as fast as anything else.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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