语句优化

[qqyellow]

这个语句如何优化，两个表里的数据都有几十万条，谢谢。select 'B'+ENTRY_ID,convert(varchar(10),P_DATE,120) P_DATE,OWNER_NAME,AGENT_NAME from (
select a.ENTRY_ID,P_DATE,OWNER_NAME,AGENT_NAME from
SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_HEAD
a
left join
SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_WORKFLOW
b
on a.ENTRY_ID=b.ENTRY_ID
where b.step_id='70000000'
and P_DATE<convert(datetime,DATEADD(day, -3, getdate())) and b.ENTRY_ID like '2346%') entry
where ENTRY_ID not in (select sCCF_NO from ALL_CCF_HEAD) order by convert(varchar(10),P_DATE,120) desc

[水族杰纶]

ENTRY_ID
step_id
P_DATE
等字段建立索引

[pt1314917]

--语句上优化不了多少了，try:
select 'B'+ENTRY_ID,convert(varchar(10),P_DATE,120) P_DATE,OWNER_NAME,AGENT_NAME 
from 
( 
select a.ENTRY_ID,P_DATE,OWNER_NAME,AGENT_NAME from  
SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_HEAD 
a 
        left join 
SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_WORKFLOW 
b 
on a.ENTRY_ID=b.ENTRY_ID 
where b.step_id='70000000' 
and datediff(dd,P_DATE,getdate())>3 and b.ENTRY_ID like '2346%') entry 
where not exists(select 1 from ALL_CCF_HEAD where sCCF_NO=entry.ENTRY_ID)
order by P_DATE desc

--另外试试把两个表的ENTRY_ID字段加上索引，sCCF_NO，P_DATE这个也加上。

[昵称被占用了]

Try:

select 'B'+a.ENTRY_ID,convert(varchar(10),P_DATE,120) P_DATE,OWNER_NAME,AGENT_NAME 
from SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_HEAD a 
     left join SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_WORKFLOW b 
     on a.ENTRY_ID=b.ENTRY_ID 
     left join ALL_CCF_HEAD c
     on a.ENTRY_ID=c.sCCF_NO
where b.step_id='70000000' 
and P_DATE <convert(datetime,DATEADD(day, -3, getdate())) 
and a.ENTRY_ID like '2346%'
and b.ENTRY_ID like '2346%'
and c.sCCF_NO is null
order by convert(varchar(10),P_DATE,120) desc

[中国风]

select 
	[ENTRY_ID]='B'+a.ENTRY_ID,
	[P_DATE]=convert(varchar(10),P_DATE,120),
	OWNER_NAME,
	AGENT_NAME 
from 
	SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_HEAD a
left join
	SICSOFTH2000.H2000_IMS_RECV_2008.dbo.ENTRY_WORKFLOW b
on a.ENTRY_ID=b.ENTRY_ID
where 
	b.step_id='70000000'
and P_DATE <DATEADD(day, -3, getdate()) and b.ENTRY_ID like '2346%' 
and not exists(select 1 from ALL_CCF_HEAD where sCCF_NO=a.ENTRY_ID)--a.ENTRY_ID這個那個表的字段
order by [P_DATE] desc

[昵称被占用了]

再检查索引，注意P_DATE在哪个表从语句看不出来
如果在ENTRY_WORKFLOW 表，需要检查有没有索引
ENTRY_WORKFLOW （ENTRY_ID ,step_id,P_DATE ）
ENTRY_HEAD (ENTRY_ID )
ALL_CCF_HEAD (sCCF_NO)
否则需要检查
ENTRY_WORKFLOW （ENTRY_ID ,step_id ）
ENTRY_HEAD (ENTRY_ID ,P_DATE)
ALL_CCF_HEAD (sCCF_NO)