Question on Efficiency of Subquery

ozzii · #1 (**permalink**) 08-31-10, 16:18

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?

Pat Phelan · #2 (**permalink**) 08-31-10, 17:26

Quote:

Originally Posted by ozzii

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

isnt this inefficient?

Not if it is the most efficent way to compute what you need.

Quote:

Originally Posted by ozzii

Is there any other way to do this more efficiently without redesigning the database?

Probably, depending on what you need.

-PatP

blindman · #3 (**permalink**) 09-01-10, 10:21

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?

ozzii · #4 (**permalink**) 09-02-10, 14:18

Quote:

Originally Posted by blindman

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.

r937 · #5 (**permalink**) 09-02-10, 18:25

Quote:

Originally Posted by ozzii

... there can be duplicates.

there was another guy who has a similar problem as yours -- duplicates

see Aggregate Incorrect with INNER JOIN

blindman · #6 (**permalink**) 09-02-10, 23:26

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech