SQL Server“ORDER BY”优化 - 大规模性能下降
使用SQL Server 2000.我有一张表从一个遗留系统每天接收一个转储,我试图编写一个查询来处理这个表中的一些引用表连接和一个order by子句。SQL Server“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条。
我在列上有一个非聚集索引。
当我运行此查询没有order by associate_id
子句大约需要2秒。使用order by
条款需要一整分钟!
我已经尝试将where
子句列与一起添加到索引,但这并没有改变性能。
执行计划的无需order by
结束看起来是这样的:
使用order by
,并行踢通过参数的顺序上,它看起来像这样:
我想也许这是weird SQL Server 2000 parallelism handling,但增加了(maxdop 1)
提示使查询花了3分钟反而!
对于我来说,在应用程序代码中进行排序并不是很明智,因为这个查询在再次运行之前缓存了大约6个小时,我将不得不每分钟在应用程序代码中对它进行排序。
我必须缺少一些非常基本的东西,但在查询一小时后,即使运行了10次后,我看不到它是什么了。当u删除所有外如果该工程快的话,我会去的子选择的选择的
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
等内部
会发生什么连接和ofcourse在有选择的..
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
。它会更快,因为左外部加入他们在从条款
Thanks @rfcdejong。我没有使用nexted选择,但这导致我正确的答案。我发现查询花了不到一秒钟的时间,但是没有连接,而且我做了子查询的invoice_detail表中实际上有很多行,但被不同的行所掩盖。我在我使用的两列上添加了一个索引,现在速度很快。 – Geronimo 2012-02-26 00:22:58
表别名不仅适用于JOIN标准 - 那么我不会问'associate_id'从哪里来看它是否是fr om其中一个OUTER JOIN(这意味着它可能是NULL)。 – 2012-02-25 23:42:30
@OMGPonies我添加了转储表别名以便更清晰 – Geronimo 2012-02-25 23:48:12