SQL Server "ORDER BY"优化 - 性能大幅下降

Question

使用 SQL Server 2000。我有一个每天从旧系统接收转储的表，我正在尝试编写一个查询，该查询将使用一些引用表连接和一个 order by 子句来处理该表。

这是我拥有的 SQL:

select  d.acct_no,
        d.associate_id,
        d.first_name,
        d.last_name,
        d.acct_bal,
        plr.long_name p_lvl,
        tlr.long_name t_lvl,
        d.category,
        d.status,
        tm.site_name,
        d.addr1 + ' ' + isnull(d.addr2,'') address,
        d.city,
        d.state,
        d.country,
        d.post_code,
        CASE WHEN d.home_phone_ok = 1 THEN d.home_phone END home_phone,
        CASE WHEN d.work_phone_ok = 1 THEN d.work_phone END work_phone,
        CASE WHEN d.alt_phone_ok = 1 THEN d.alt_phone END alt_phone,
        CASE WHEN d.email_ok = 1 THEN d.email END email,
        d.last_credit last_paid,
        d.service,
        d.quantity,
        d.amount,
        ar.area_desc area
from    item_dump d
        left outer join territory_map tm on tm.short_postcode = left(post_code,3) and country in ('United States','Canada')
        left outer join p_level_ref plr on plr.p_level_id = d.p_lvl_id
        left outer join t_level_ref tlr on tlr.t_level_id = d.t_lvl_id
        left outer join (select distinct master_item_id, site_item_id from invoice_detail) as map on map.item_id = d.item_no
        left outer join item_ref i on i.item_id = map.master_item_id
        left outer join area_ref ar on ar.area_id = i.area_id
where   (d.cat_id > 80 or d.cat_id < 70)
        and d.standing < 4
        and d.status not like 'DECEASED'
        and d.paid = 1
order by d.associate_id

大多数这些列直接来自遗留系统转储表 item_dump .所有的连接都只是几行的引用表。旧表本身有大约 17000 条记录，但使用 where 语句查询出来的结果是 3000 条。

我在 associate_id 上有一个非聚集索引柱子。

当我在没有 order by associate_id 的情况下运行此查询时条款大约需要2秒钟。与 order by条款需要整整一分钟!

我试过添加 where子句列与 associate_id 一起添加到索引中但这根本没有改变性能。

没有 order by的执行计划结束看起来像这样:



使用 order by , 并行性以参数顺序开始，它看起来像这样:



我想也许是 weird SQL Server 2000 parallelism handling ，但添加 (maxdop 1)提示使查询需要 3 分钟!

将排序放入应用程序代码中对我来说并不明智，因为此查询在再次运行之前会缓存大约 6 个小时，而且我必须每分钟在应用程序代码中对其进行多次排序。

我一定遗漏了一些非常基本的东西，但是在查询一个小时后，即运行它 10 次后，我再也看不到它是什么了。

Answer 1

当您删除所有外部连接并且当然选择在那里时会发生什么..

select  d.acct_no,
        d.associate_id,
        d.first_name,
        d.last_name,
        d.acct_bal,
        d.category,
        d.status,
        d.addr1 + ' ' + isnull(d.addr2,'') address,
        d.city,
        d.state,
        d.country,
        d.post_code,
        CASE WHEN d.home_phone_ok = 1 THEN d.home_phone END home_phone,
        CASE WHEN d.work_phone_ok = 1 THEN d.work_phone END work_phone,
        CASE WHEN d.alt_phone_ok = 1 THEN d.alt_phone END alt_phone,
        CASE WHEN d.email_ok = 1 THEN d.email END email,
        d.last_credit last_paid,
        d.service,
        d.quantity,
        d.amount
from    item_dump d
where   (d.cat_id > 80 or d.cat_id < 70)
        and d.standing < 4
        and d.status not like 'DECEASED'
        and d.paid = 1
order by d.associate_id

如果这工作得很快，那么我会在选择的内部进行子选择

select  d.acct_no,
        d.associate_id,
        d.first_name,
        d.last_name,
        d.acct_bal,
        plr.long_name p_lvl,
        tlr.long_name t_lvl,
        d.category,
        d.status,
        (select tm.site_name 
         from territory_map tm 
         where tm.short_postcode = left(post_code,3) 
           and country in ('United States','Canada') as site_name

等等。当左外在 from 子句中加入它们时，它会更快