优化查询

Question

select distinct right(AUFNR,7) as orderNumber
               ,auart 
               ,case when exists(select top 1 1 from covp where kstar = '0060305114' and OBJNR = a.OBJNR) 
               then '非正常' else '正常' end as orderType
               ,case (select top 1 left(MATNR,1) from covp where KSTAR = '0060392221' and OBJNR = a.OBJNR and MATNR <>'')
               when '3' then 'A'
               when '5' then 'B'
               else null 
               end AS BU
from aufk a
where a.AUFNR not like 'Q%'
这代码还能优化吗？ covp 表有2000多万条数据，aufk 有400多万
现在跑一次要20多分钟

Answer 1

select kstar ,OBJNR ,MATNR  
into #temp
from covp where kstar in( '0060305114' ,'0060392221')select distinct right(AUFNR,7) as orderNumber
               ,auart 
               ,case when exists(select top 1 1 from #temp where kstar = '0060305114' and OBJNR = a.OBJNR) 
               then '非正常' else '正常' end as orderType
               ,case (select top 1 left(MATNR,1) from #temp where KSTAR = '0060392221' and OBJNR = a.OBJNR and MATNR <>'')
               when '3' then 'A'
               when '5' then 'B'
               else null 
               end AS BU
from aufk a
where a.AUFNR not like 'Q%'
先暂时这样改，看能否提高速度

Answer 2

忘记说了，是View，不是Procedure,不能用#temp吧

Answer 3

对 aufk 表的 aufnr,objnr; covp 表的 kstar 创建索引后再试.

Answer 4

条件不允许，会影响到ETL速度

Answer 5

呵呵，#temp用不了的话也慢慢跑吧

Answer 6

where a.AUFNR not like 'Q%'
改成
where left(a.AUFNR, 1)<>'Q'
这个会不会好点？

Answer 7

Select distinct right(A.AUFNR,7) as orderNumber,A.auart ,
Case When isNull(C1.Objnr,'')='' Then '非正常' else '正常' end as orderType,
Case C2.m1 when '3' then 'A' when '5' then 'B' else null  end AS BU 
From aufk A Left Join covp C1 On (A.Objnr = C1.Objnr And C1.kstar = '0060305114')
Left Join (select top 1 left(MATNR,1) as m1, Objnr from covp) C2 
On (A.Objnr = C2.Objnr And C2.kstar = '0060392221' And MATNR <> '')
where Left(a.AUFNR,1) <>  'Q'

Answer 8

with temp
as(
select kstar ,OBJNR ,MATNR  
from covp where kstar in( '0060305114' ,'0060392221'))select distinct right(AUFNR,7) as orderNumber
               ,auart 
               ,case when exists(select top 1  from temp where kstar = '0060305114' and OBJNR = a.OBJNR) 
               then '非正常' else '正常' end as orderType
               ,case (select top 1 left(MATNR,1) from temp where KSTAR = '0060392221' and OBJNR = a.OBJNR and MATNR <>'')
               when '3' then 'A'
               when '5' then 'B'
               else null 
               end AS BU
from aufk a
where a.AUFNR not like 'Q%'
2005 版本往上可以用表表达式

Answer 9

不能temp，或许你可以做一个表专门来存储
select kstar ,OBJNR ,MATNR  
from covp where kstar in( '0060305114' ,'0060392221')
的数据，若这也不能做到，也就是说不能用空间换时间不能索引，也就是你爱用表扫描有两个方案可以解决你的麻烦
1、请采购100倍以上性能的server
2、采用nosql，索引对你再无意义当服务器性能有限时，你想优化一个对上千万级别的表，一般手段都是index不用index,那就别用关系数据库吧，大表的查询，它很依赖索引

Answer 10

楼主可以在9楼的基础上把错误修正，看看效率如何。个人觉得9楼的方法比较好，因为在case 语句中去判断exists 这种方法性能不会好。