代码之家 › 专栏 › 技术社区 › John Leidegren

SQL Server搜索筛选和按性能排序问题

tsql sql-server-2005

John Leidegren · 技术社区 · 15 年前

我们有一个表值函数,它返回您可以访问的人员列表,并且我们有一个搜索和一个名为search result的人员之间的关系。

我们要做的是不要从搜索中选择所有人并呈现他们。

查询如下所示

SELECT qm.PersonID, p.FullName 
FROM QueryMembership qm
INNER JOIN dbo.GetPersonAccess(1) ON GetPersonAccess.PersonID = qm.PersonID
INNER JOIN Person p ON p.PersonID = qm.PersonID
WHERE qm.QueryID = 1234

QueryID=1234的行只有25行,但QueryMembership表中总共有将近500万行。人桌上有大约4万人。

queryid不是pk,但它是一个索引。查询计划告诉我,总成本的97%是用seek谓词进行“键查找”。

QueryMembershipID = Scalar Operator (QueryMembership.QueryMembershipID as QM.QueryMembershipID)

为什么查询中根本不使用pk呢?为什么要花这么长时间?

总共有25个人,有了索引,这应该是一个表扫描,查找queryid=1234的所有queryMembership行,然后是表值函数中存在的25个人的联接。哪个BTW只需要评估一次,在不到1秒的时间内完成。

5 回复 | 直到 15 年前

Chaowlert Chaisrichalermpol 15 年前

如果要避免“键查找”,请使用覆盖索引

create index ix_QueryMembership_NameHere on QueryMembership (QueryID)
include (PersonID);

添加更多列名 include 论据。

关于这一点,为什么pk的“密钥查找”工作如此缓慢,请尝试 DBCC FREEPROCCACHE , ALTER INDEX ALL ON QueryMembership REBUILD , ALTER INDEX ALL ON QueryMembership REORGANIZE

如果您的pk的索引被禁用,或者缓存保持错误的计划,这可能会有帮助。

Oded 15 年前

您应该在查询的表上定义索引。尤其是在 WHERE 和 ORDER BY 条款。

使用 Database Tuning Advisor 查看SQL Server的建议。

Dave Markle 15 年前

当然,对于细节,您需要发布查询和表设计。

但我得说几点:

你已经得出结论,慢行是order by子句的结果。我怀疑。真正的测试是是否通过加快查询速度来删除订单,而您还没有这样做。美元换甜甜圈,没什么区别。
只有当优化器实际选择使用您定义的索引时,才能在big-o声明中获得“log n”。这可能不会发生,因为你的索引可能没有足够的选择性。使临时表解决方案比优化器的解决方案更快的是,您知道优化器没有返回的数据子集(特别是,它是数据的一个非常小的子集)。如果索引对查询的选择性不够强,优化器就不能总是合理地假设这一点,它将选择一个计划,避免出现它认为可能出现的最坏情况:大量索引查找,随后是大量查找,然后是一个大排序。它常常选择扫描和散列。所以你对临时表所做的通常是解决这个问题的一种方法。通常,您可以缩小索引范围或在要处理的数据子集上创建索引视图。这一切都取决于你孩子的具体情况。

Ben Hoffman 15 年前

你需要你的 WHERE 和 ORDER BY 条款。我不是专家,但我敢打赌,它正在为每一行进行表扫描。因为你的速度问题是通过移除 INNER JOIN 或 订购 我敢打赌这个问题是和join有关的。我敢打赌它是在你的连接表上做表扫描,因为排序。通过在 哪里 第一条你可以看看事实是否如此。

Thomas 15 年前

您是否尝试将查询重组为CTE以分离TVF呼叫?所以,有点像:

With QueryMembershipPerson
    (
    Select QM.PersonId, P.Fullname
    From QueryMembership As qm
        Join Person As P
            On P.PersonId = QM.PersonId
    Where QM.QueryId = 1234
    )
Select PersonId, Fullname
From QueryMembershipPerson As QMP
    Join dbo.GetPersonAccess(1) As PA
        On PA.PersonId = QMP.PersonId

编辑 :btw,我假设querymembership和person表中都有personid的索引。

编辑两个表表达式怎么样:

With 
    QueryMembershipPerson As
    (
    Select QM.PersonId, P.Fullname
    From QueryMembership As qm
        Join Person As P
            On P.PersonId = QM.PersonId
    Where QM.QueryId = 1234
    )
    , With PersonAccess As
    (
    Select PersonId
    From dbo.GetPersonAccess(1) 
    )
Select PersonId, Fullname
From QueryMembershipPerson As QMP
    Join PersonAccess As PA
        On PA.PersonId = QMP.PersonId

另一个解决方案是这样的派生表:

Select ...
From  (
        Select QM.PersonId, P.Fullname
        From QueryMembership As qm
            Join Person As P
                On P.PersonId = QM.PersonId
        Where QM.QueryId = 1234
        ) As QueryMembershipPerson
    Join dbo.GetPersonAccess(1)  As PA
        On PA.PersonId = QueryMembershipPerson.PersonId

如果将一些查询推送到临时表中,然后加入到该表中,我会惊讶地发现您不能将该概念组合到CTE或带有派生表的查询中。