Distinct Keyword

[Matt]

Hi All,

I have a query that pulls in data from 3 different tables. When I run
the query without using the DISTINCT keyword, the query runs in about
20 seconds. When I run the query with the DISTINCT keyword it takes
about 10 minutes. I can't imagine that this is the the real reason
that my query is hanging for so long, but I don't have any other
explaination.

Has anyone else had this problem? Does it seem like something else is
wrong here?

Note: The query does contain a subquery, which I originally thought
might be the reason that the query is hanging. After playing around I
have found that the subquery only slows it down a little. Would the
combination of a subquery and DISTNCT give me this problem?


Thanks for any and all advice!!!!

 

[Patrice]

Though DISTINCT does some additional work to remove duplicates it seems
surprising the cost is so high in your case. Do you see any other difference
(such as in the number of records in the result set) ?

Generally my personal preference is to avoid DISTINCT as much as possible
(i.e. if I don't want duplicates, the query is built to avoid returning
duplicates rather than to include them and having to use distinct to then
remove them).

Try to simply at much as possible (even if this is not the result you want)
to find out the last change that gives similar time with and without
distinct (or start with the base table and adds until you are seeing a
difference).

 

[Matt]

Though DISTINCT does some additional work to remove duplicates it seems
surprising the cost is so high in your case. Do you see any other difference
(such as in the number of records in the result set) ?

Generally my personal preference is to avoid DISTINCT as much as possible
(i.e. if I don't want duplicates, the query is built to avoid returning
duplicates rather than to include them and having to use distinct to then
remove them).

Try to simply at much as possible (even if this is not the result you want)
to find out the last change that gives similar time with and without
distinct (or start with the base table and adds until you are seeing a
difference).

I have rebuilt the query from scratch and it runs great up until I
enter the DISTINCT keyword. It really doesn't make sense to me that
this would cause such a difference.

I think it is something I wil just have to live with for now!!!


Thanks for your insight!