选择SELECT DISTINCT与我的老板解决分歧 [英] Settle a disagreement with my boss re SELECT DISTINCT

问题描述

我的老板坚持认为SELECT DISTINCT是一个比

选择所有其他因素相同的查询更快的查询。我不同意。我们将一个

Access前端链接到一个SQL Server后端，通常只返回一个记录的
。她说，一旦找到匹配的记录，查询就会停止

。我们俩都是数据库理论中的相对新手

（显然）。


有人能帮忙解决这个问题吗？

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?

推荐答案

mathilda写道：

mathilda wrote:

我的老板一直坚持认为SELECT DISTINCT是一个比选择所有其他因素相同的查询更快的查询。我不同意。我们将访问前端链接到SQL Server后端，通常只返回一条记录。她说，一旦找到匹配的记录，查询就会停止。我们俩都是数据库理论中的相关新手（显然）。

有人可以帮忙解决这个问题吗？

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?

它会有所不同从查询到查询，但是我希望DISTINCT能够更慢或者平局，或者更快。如果您的查询是非智能，足够

强制进行表扫描，无论你是否b $ b都有DISTINCT子句，它都会这样做。毕竟，发动机不会知道发动机。那个

你只是在寻找一条记录而且它不知道其他行是否重复，直到它们被查看之后才重复。


也许如果添加了TOP 1条款？

-

我没有查看附带的电子邮件帐号

到这条消息。发送给... ...

在Hunter dot com的RBrandt

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 "unintelligent" 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

谢谢，看起来我赢了。但是你想要的意思是强迫一张桌子

扫描

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

mathilda写道：

mathilda wrote:

我的老板一直坚持认为SELECT DISTINCT是一个比所有其他因素相同的查询更快的查询。我不同意。我们将访问前端链接到SQL Server后端，通常只返回一条记录。她说，一旦找到匹配的记录，查询就会停止。我们俩都是数据库理论中的相对新手（显然）。

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).

这看起来完全不合逻辑。


从BOL：DISTINCT关键字消除了SELECT语句的

结果中的重复行。如果未指定DISTINCT，则返回所有行

，包括重复项。


因此，如果结果集中只有一行，那么没有

消除。如果有更多，那么他们将不得不进行测试

duplicity（！）。在第一种情况下，任何差异都是不明显的;

在第二种情况下差异可能是原子钟明显的。


当然如果你正在使用别人会知道的ODBC。


-

Lyle

-

来自ADO28。 chm


不推荐使用的组件

以下每个组件都被认为已过时。虽然这个版本的微软仍然支持这些

组件？数据

访问组件（MDAC），将来可能会将其删除。当

编写新的应用程序时，你应该避免使用这些已弃用的

组件。修改现有应用程序时，强烈建议您删除对这些组件的依赖。


ODBC提供程序（MSDASQL）

强烈建议您使用其中一个本机OLE DB提供程序

而不是Microsoft开放式数据库连接（ODBC）提供程序。

本机OLE DB提供程序提供更好的应用程序稳定性和

表现。此外，未来将在

中支持本机OLE DB提供程序，而MSDASQL将不会添加任何新功能，

将无法在64位上使用，并且无法从OLE获取

DB NET数据提供者。


.....

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.

.....

这篇关于选择SELECT DISTINCT与我的老板解决分歧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！