SQL Server“ORDER BY”优化 - 大幅降低性能

Using SQL Server 2000. I have a table that receives a dump from a legacy system once a day, I am trying to write a query that will process this table with a few reference table joins and an order by clause.

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

This is the SQL I have:

这是我的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

Most of these columns are straight from the legacy system dump table item_dump. All the joins are only reference tables with few rows. The legacy table itself has about 17000 records but with the where statements the query comes out to 3000.

这些列中的大多数都是遗留系统转储表item_dump。所有连接只是几行的引用表。遗留表本身有大约17000条记录,但是where语句的查询结果为3000条。

I have a non-clustered index on the associate_id column.

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

When I run this query without the order by associate_id clause it takes about 2 seconds. With the order by clause it takes a full minute!

当我在没有associate_id子句的命令的情况下运行此查询时,大约需要2秒钟。使用order by子句需要整整一分钟!

I've tried adding the where clause columns to the index along with associate_id but that didn't change the performance at all.

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

The end of the execution plan without the order by looks like this:

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

Using order by, parallelism kicks in on the order by argument and it looks like this:

使用order by,parallelism按顺序启动顺序,它看起来像这样:

I thought maybe it was weird SQL Server 2000 parallelism handling, but adding the (maxdop 1) hint made the query take 3 minutes instead!

我想也许这是奇怪的SQL Server 2000并行处理,但添加(maxdop 1)提示使查询花了3分钟而不是!

It isn't really sensible for me to put sorting in the application code because this query caches for about 6 hours before it gets run again and I would have to sort it in the application code many times a minute.

对我来说,在应用程序代码中放置排序是不明智的,因为这个查询在再次运行之前会缓存大约6个小时,而且我必须每分钟在应用程序代码中对它进行多次排序。

I must be missing something very basic but after straining at the query for an hour i.e. running it 10 times, I can't see what it is anymore.

我必须遗漏一些非常基本的东西,但是在查询了一个小时后,即运行了10次,我看不出它是什么了。

1 个解决方案

#1

What happens when u remove all the outer joins and ofcourse the select's in there..

当你移除所有外部连接并且选择那里的选择时会发生什么。

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

If that works fast then i would go for sub selects inside the select's

如果它工作得很快,那么我会去选择内部的子选择

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

etc. it'll be really faster as left outer joining them in the from clause

因为在外部将它们连接在from子句中,所以它会更快