大家帮忙优化一个Sql(检索时间过长) 急
select ROW_NUMBER() OVER(order by B1.strDepartmentId,B1.strClientNo) limit,
A1.strClientId,
A1.strClientAttachType,
B1.strClientNo,
B1.strClientShowName,
C1.strDepartmentName,
Q1.strSalesTeamShowName,
Q2.strMiddleManShowName,
Q3.strSalesManShowName
from t_crm_clientattach_relation A1
left join t_crm_client_info B1 on B1.strClientId=A1.strClientId
left join t_crm_salesteam_info Q1 on A1.strSalesTeamId=Q1.strSalesTeamId
left join t_crm_middleman_info Q2 on A1.strMiddlemanId=Q2.strMiddleManId
left join t_crm_salesman_info Q3 on A1.strSalesManId=Q3.strSalesManId
left join (
select A2.* from t_sys_department_info A2
left join t_crm_client_info B2 on A2.strDepartmentNo=B2.strDepartmentId
) C1 on B1.strDepartmentId=C1.strDepartmentId
left join (
select A2.*
from t_sys_code_value A2
left join t_sys_code_sort B2 on A2.strCodeSortId=B2.strCodeSortId
where B2.strCodeSortNo='0108'
) D1 on A1.strClientAttachType = D1.strCodeValueNo
where 1=1
and A1.strCurrentStatus ='00'
and A1.strClientAttachType !='00'
and B1.strClientNo is not null
and C1.strDepartmentId = 'SYSBMXX000002'
------解决方案--------------------链接字段加索引。 另外得看数据量多少了。
t_sys_department_info
t_crm_client_info
t_sys_code_value
t_sys_code_sort
------解决方案--------------------可以把你的嵌套放进一个临时表来处理
另外在连接字段加索引
------解决方案--------------------首先你确定都是要用left join吗?是不是可以用inner join代替?这要看你的业务需求.
and B1.strClientNo is not null 这个条件不能用到索引
像这种 C1.strDepartmentName只取了一个字段,下面这个查询就没有必要把所有的字段都带出来.
select A2.* from t_sys_department_info A2
left join t_crm_client_info B2 on A2.strDepartmentNo=B2.strDepartmentId
) C1 on B1.strDepartmentId=C1.strDepartmentId
先处理下这些再试下.
------解决方案--------------------可以查看查询计划,看看编译期间,查询计划有没有用到你的索引