优化查询,该如何解决

优化查询

SQL code

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 BUfrom aufk awhere a.AUFNR not like 'Q%'

SQL code

select kstar ,OBJNR ,MATNR  into #tempfrom 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 BUfrom aufk awhere a.AUFNR not like 'Q%'
[解决办法]
对 aufk 表的 aufnr,objnr; covp 表的 kstar 创建索引后再试.
[解决办法]
探讨

引用:

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

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

[解决办法]
探讨

引用:
引用:

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

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


那您就慢慢跑吧.

[解决办法]
探讨


引用:
引用:
引用:

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

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


那您就慢慢跑吧.

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

[解决办法]
where a.AUFNR not like 'Q%'
改成
where left(a.AUFNR, 1)<>'Q'
这个会不会好点？
[解决办法]
SQL code
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'
[解决办法]
SQL code
with tempas(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 BUfrom aufk awhere a.AUFNR not like 'Q%'
------解决方案-------------------- 
 
不能temp，或许你可以做一个表专门来存储
select kstar ,OBJNR ,MATNR 
from covp where kstar in( '0060305114' ,'0060392221')
的数据，若这也不能做到，也就是说不能用空间换时间

不能索引，也就是你爱用表扫描

有两个方案可以解决你的麻烦
1、请采购100倍以上性能的server
2、采用nosql，索引对你再无意义

当服务器性能有限时，你想优化一个对上千万级别的表，一般手段都是index

不用index,那就别用关系数据库吧，大表的查询，它很依赖索引
[解决办法]
楼主可以在9楼的基础上把错误修正，看看效率如何。个人觉得9楼的方法比较好，因为在case 语句中去判断exists 这种方法性能不会好。