Re: "distinct" keyword use in sql queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/06/04 

Date: Fri, 06 Aug 2004 12:04:12 +0200

On 6 Aug 2004 02:46:08 -0700, ?scar Martins wrote:

>Hi!
>
>Is there any kind of worry when using the keyword "distinct" in sql
>commands for querying DB Sqlserver 8??
>Some people higher experienced than me in vb.net apps development told
>me not to use "distinct" but they mentioned no reason for that...
>I´m struggling to not use "distinct"... Is that worth??
>
>thanks in advance.

Hi ?scar,

The one reason to avoid using DISTINCT when it's not needed is that it
decreases the query's performance. The usual technique for executing a
DISTINCT is sorting the intermediate results and then removing rows that
are equal to the row immediately before it. There are other techniques
that SQL Server's query optimizer will use if they appear to be cheaper,
but at the end of the day, looking for and filtering out duplicates will
always require extra work to be carried out by the DB. The performance hit
may vary between queries, but there will always be SOME impact.

Of course, if a query CAN give unwanted duplicates, you will need to
include DISTINCT or use another technique to get rid of duplicates, but I
agree that slapping DISTINCT onto every query "just in case" is bad
practice.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Deleting duplicate records/same table
    ... append the tblUniqueRecords fields into the fields on tblMaster. ... The new table without the duplicates is called ... Prepared By is the type of query I was ...
    (microsoft.public.access.queries)
  • Re: Query to delete duplicate records - but NOT original
    ... > unique records - I've already got an update query to grab the unique ... > hsc but one other field different - it ignores those 2. ... but this method of removing duplicates requires it. ... >> Access Database Samples: www.rogersaccesslibrary.com ...
    (microsoft.public.access.queries)
  • RE: Want to edit a table based on a query of a query of several qu
    ... First create union query like this -- ... Use that query in design view and add all of the other nine tables. ... This puts all the data in to a single query output without duplicates. ... Then I run a big query of all 9 queries to consolidate the data into one big ...
    (microsoft.public.access.queries)
  • Re: correct way to write the syntax for a Dlookup in a query expression in VB
    ... those that return false when the query runs to another table and leave ...    c) that you rewrite your sql select as a join checking for nulls ... DLookup; I am not). ... the duplicates may include differing information in particular ...
    (comp.databases.ms-access)
  • Re: Problem with delete duplicates query
    ... You might look at the Help topic called "Delete a group of records with a query". ... the final query to actually delete the records (the query based on the table and the query identifying the duplicates), I get a message that "Could not delete from specified tables". ... and then attach an Autonumber field to serve as the primary key and delete using that. ... FROM Table1 AS T1 ...
    (microsoft.public.access.queries)