Settle a disagreement with my boss re SELECT DISTINCT

[mathilda]

My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are only
returning one record. She states that with disctinct the query stops
as soon as it finds a matching record. Both of us are relative novices
in database theory (obviously).

Can someone help settle this?

[Rick Brandt]

mathilda wrote:

My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are only
returning one record. She states that with disctinct the query stops
as soon as it finds a matching record. Both of us are relative
novices in database theory (obviously).

Can someone help settle this?

It would vary from query to query, but I would expect DISTINCT to either be
slower or a tie, never faster. If your query is "unintellig ent" enough to
force a table scan then it is going to do that regardless of whether you
have the DISTINCT clause or not. After all, the engine wouldn't "know" that
you are only looking for one record and it won't know if other rows are
duplicates until AFTER they have been looked at.

Perhaps if a TOP 1 clause were added?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[mathilda]

Thanks, looks like I win. But want do you mean by "forcing a table
scan"

[Lyle Fairfield]

mathilda wrote:

My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are only
returning one record. She states that with disctinct the query stops
as soon as it finds a matching record. Both of us are relative novices
in database theory (obviously).

This seems entirely illogical.

From BOL: "The DISTINCT keyword eliminates duplicate rows from the
results of a SELECT statement. If DISTINCT is not specified, all rows
are returned, including duplicates."

So if there is only one row in the result set then there is none to
eliminate. If there are more then they will have to be tested for
duplicity (!). In the first case any difference would be unnoticeable;
in the second case the difference might be noticeable to an atomic clock.

Of course if you are using ODBC someone else would know.

--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

.....

[Rick Brandt]

mathilda wrote:

Thanks, looks like I win. But want do you mean by "forcing a table
scan"

If your query's WHERE clause is against an indexed field then only the index
is scanned and then the matching rows retrieved. If it is against a
non-indexed field then the entire table is scanned to look for matches.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Nicole]

mathilda wrote:

My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are only
returning one record. She states that with disctinct the query stops
as soon as it finds a matching record. Both of us are relative novices in database theory (obviously).

Can someone help settle this?

You are correct: the DISTINCT command will actually slow the query.
First, the SELECT command is performed, and then the result set is
further processed to return the DISTINCT result only. SQL Server
processes DISTINCT like an aggregate function, it doesn't just stop
when it gets to one result.

The proof in in the pudding: open Query Analyzer, tell it to show the
Execution plan and then run the query with and without DISTINCT.
Probably the Execution Time will read "0:00:00" for both queries, but
you can show your boss the extra processing step.

[Albert D. Kallal]

"mathilda" <sm***********@ yahoo.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are only
returning one record.

There is some important things to be aware of here:

* since you are using sql server, then when you return ONLY one record
(either example....via a condition, or by the distinct) only ONE record is
transfer down the wire. Thus, assuming that both approaches can readably
return the ONE record in a short time, you as a end user likely will not
experience ANY difference. In other words, the LARGE time issue here is
transferring the one record...not that time of finding the one record. It is
possible that one approach might be faster then the other (say 1 / 10,000 of
a second), however, the time taken to transfer the data is a few 100's of s
second, so that 10,000 times faster to FIND and retrieve the record will NOT
be noticed by you. The MOST important issue here is that ONE record is being
returned down the wire.

* So, we have two issues:
Time to transfer the record (AFTER it has been found)
Time to find the ONE record, either via Distinct, or by some keyID
etc.

I have little doubt that using a condition to return one record is
considerably faster then telling the database to figure things out, and
NEVER return duplicates. Telling the database system to not return
duplicates
takes a lot of work. If you can return the record via:

select * from tblCustomer where custid = 123

The above is most certainly better then:

select distinct * from tblCustomer where custid = 123

However, adding the distinct keyword in the above is NOT going to improve
speed if ONLY one record exists. The time difference would be minimal..but
adding distinct would increase things by some amount...but likely not even a
amount that you can measure).

However, what about when there is going to be multiple records returned, and
we ONLY want one record?

Again:

select top 1 from tblCustomer where custid = 123

This I think again would be faster then distinct, with distinct all possible
matches
of custID = 123 have to be tested. There could be 50, or even 2000 records
with a custid = 123....and making distinct work on that can really cost.
With
a top 1, then only the first match need be returned.

However, you question was about one record, and all things being
equal. So, if only one record is to be normally retuned, and the distinct is
NOT needed, then I would leave it out. I am CERTAIN that putting
in distinct is NOT faster. The amount that distinct would be slower
by is likely not measure in this case, .but it would be a tiny tiny bit
slower.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.shaw.ca/AlbertKallal

[Dimitri Furman]

On Feb 09 2005, 10:36 am, Lyle Fairfield <ly******@yahoo .ca> wrote in
news:M5******** ***********@rea d2.cgocable.net :

So if there is only one row in the result set then there is none to
eliminate. If there are more then they will have to be tested for
duplicity (!). In the first case any difference would be unnoticeable;
in the second case the difference might be noticeable to an atomic clock.

Actually the difference may be quite noticeable in the second case,
depending on the size of data set. This is similar to the difference (!)
between UNION and UNION ALL.

Paste the following into QA, hit Ctrl+L, and compare the plans:

use pubs

select state
from dbo.authors
go

select distinct state
from dbo.authors
go

--
remove a 9 to reply by email

[Lyle Fairfield]

Dimitri Furman wrote:

On Feb 09 2005, 10:36 am, Lyle Fairfield <ly******@yahoo .ca> wrote in
news:M5******** ***********@rea d2.cgocable.net :

So if there is only one row in the result set then there is none to
eliminate. If there are more then they will have to be tested for
duplicity (!). In the first case any difference would be unnoticeable;
in the second case the difference might be noticeable to an atomic clock.

Actually the difference may be quite noticeable in the second case,
depending on the size of data set. This is similar to the difference (!)
between UNION and UNION ALL.

Paste the following into QA, hit Ctrl+L, and compare the plans:

use pubs

select state
from dbo.authors
go

select distinct state
from dbo.authors
go

I feel
"We are linking an
Access front end to a SQL Server back end and normally are only
returning one record."
implies a very small number of records.

--
--
Lyle
--

[Lyle Fairfield]

Dimitri Furman wrote:

On Feb 09 2005, 10:36 am, Lyle Fairfield <ly******@yahoo .ca> wrote in
news:M5******** ***********@rea d2.cgocable.net :

So if there is only one row in the result set then there is none to
eliminate. If there are more then they will have to be tested for
duplicity (!). In the first case any difference would be unnoticeable;
in the second case the difference might be noticeable to an atomic clock.

Actually the difference may be quite noticeable in the second case,
depending on the size of data set. This is similar to the difference (!)
between UNION and UNION ALL.

Paste the following into QA, hit Ctrl+L, and compare the plans:

use pubs

select state
from dbo.authors
go

select distinct state
from dbo.authors
go

BTW the time difference is not so noticeable here.
--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....