Performance hit if I use SELECT DISTINCT?

We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code

SELECT DISTINCT email FROM Subscribers

to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).

I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.

What do you performance gurus think?Hi

Distinct does have an overhead but against 3500 records it should go unnoticed I would expect. Suck it an see - test both queries and see how they hold up.

If you are only selecting email and email is indexed (nonclustered) then you have a covered query (the data comes from the index). These values are not duplicated so I would imagine (never tested) that there would be 0 overhead for this. Someone could confirm or correct this. Either way - the overhead should be minimal.

HTH|||Thanks, I also don't think there will be a hit on this small a recordset, but I wanted to ask. As for indexing and other modifications... not today. I need a 30-second solution to this problem created in someone else's old code. These issues will be addressed in our next upgrade, which should be done next month.

Thanks again!|||DISTINCT can have a significant impact on performance, but again this will only be noticable on large datasets.sql

performance (distinct and group )

hi!
I have a doubt when the performance of some commands distinct and group by,
which is faster
some times group by is faster than distinct, somebody knows of something?
tanks
joaojoao
Some comments I have cut and pasted from a programming
good practices document
Carefully evaluate whether your query needs the DISTINCT
clause or not. The DISTINCT clause slows down virtually
every query it is in. Some developers automatically add
this clause to every one of their SELECT statements, even
when it is not necessary. This is a bad habit that should
be stopped. In addition, keep in mind that in some cases,
duplicate results in a query are not a problem. If this is
the case, then don't use a DISTINCT clause.
Don't use DISTINCT or ORDER BY in your SELECT statements
unless you really need them. Both options can add a lot of
additional overhead to your query, and they aren't always
needed for your application.
If your SELECT statement contains a HAVING clause, write
your query so that the WHERE clause does most of the work
(removing undesired rows) instead of the HAVING clause do
the work of removing undesired rows. Using the WHERE
clause appropriately can eliminate unnecessary rows before
they get to the GROUP BY and HAVING clause, saving some
unnecessary work, and boosting performance.
For example, in a SELECT statement with WHERE, GROUP BY,
and HAVING clauses, here's what happens. First, the WHERE
clause is used to select the appropriate rows that need to
be grouped. Next, the GROUP BY clause divides the rows
into sets of grouped rows, and then aggregates their
values. And last, the HAVING clause then eliminates
undesired aggregated groups. If the WHERE clause is used
to eliminate as many of the undesired rows as possible,
this means the GROUP BY and the HAVING clauses will have
less work to do, boosting the overall performance of the
query.
The GROUP BY clause can be used with or without an
aggregate function. But if you want optimum performance,
don't use the GROUP BY clause without an aggregate
function. This is because you can accomplish the same end
result by using the DISTINCT option instead, and it is
faster.
For example, you could write your query two different
ways:
USE Northwind
SELECT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
GROUP BY OrderID
or
USE Northwind
SELECT DISTINCT OrderID
FROM [Order Details]
WHERE UnitPrice > 10
Both of the above queries produce the same results, but
the second one will use less resources and perform faster.
Hope this helps
John|||John Bandettini wrote:
<snip>
> SELECT OrderID
> FROM [Order Details]
> WHERE UnitPrice > 10
> GROUP BY OrderID
> or
> SELECT DISTINCT OrderID
> FROM [Order Details]
> WHERE UnitPrice > 10
> Both of the above queries produce the same results, but
> the second one will use less resources and perform faster.
John, when I run these queries, I get exactly the same query plan. This
is what I expected. I think nowadays SQL-Server is smart enough to
determine that these two queries produce the same result, and therefore
can be handled with the same (optimum) query plan.
Gert-Jan|||In addition...
If you get a significant difference between the two, you might want to communicate this to MS. If
you have a repro, you can post it here. These two operations are the same, semantically, and if the
optimizer produces a worse plan for distinct then we have found a weakness in the optimizer.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F9440F5.9E25C9C2@.toomuchspamalready.nl...
> John Bandettini wrote:
> <snip>
> > SELECT OrderID
> > FROM [Order Details]
> > WHERE UnitPrice > 10
> > GROUP BY OrderID
> > or
> >
> > SELECT DISTINCT OrderID
> > FROM [Order Details]
> > WHERE UnitPrice > 10
> >
> > Both of the above queries produce the same results, but
> > the second one will use less resources and perform faster.
> John, when I run these queries, I get exactly the same query plan. This
> is what I expected. I think nowadays SQL-Server is smart enough to
> determine that these two queries produce the same result, and therefore
> can be handled with the same (optimum) query plan.
> Gert-Jan