多表SQL查询优化

这是一个关联5张表的查询，虽然实现了要求，但查询速度实在太慢，希望大家能帮忙优化下，谢谢了，分数不是太多，请大虾们帮帮忙！
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SELECT TOP(@PageSize)
TE_MainInfo.ShopId,
TE_MainInfo.CreateDate AS SCreateDate,
TE_Domain.DomainName,
TE_Employe.FullName,
TE_Employe.Email,
TE_Certificate.CertCode,
TE_Certificate.RegistName,
TE_Certificate.CompanyName,
TE_Certificate.FileName,
TE_Certificate.Status,
TE_Certificate.CreateDate AS CCreateDate
FROM TE_MainInfo
LEFT JOIN TE_Domain ON TE_Domain.ShopId = TE_MainInfo.ShopId
AND TE_Domain.DomainType<>1
LEFT JOIN TE_Certificate ON TE_Certificate.ShopId = TE_MainInfo.ShopId
LEFT JOIN TR_ShopEmployee ON TR_ShopEmployee.ShopId = TE_MainInfo.ShopId
INNER JOIN TE_Employe ON TE_Employe.EmployeId = TR_ShopEmployee.EmployeId
AND TE_Employe.RoleId=100
WHERE (TE_Domain.DomainName LIKE '%' + ISNULL(@Value,TE_Domain.DomainName) + '%'
   OR TE_Employe.Email LIKE '%' + ISNULL(@Value,TE_Employe.Email) + '%'
   OR TE_Certificate.RegistName LIKE '%' + ISNULL(@Value,TE_Certificate.RegistName) + '%')
   AND ISNULL(TE_Certificate.Status,0)=ISNULL(@sqlStatus,ISNULL(TE_Certificate.Status,0))
ORDER BY SCreateDate DESC
END
ELSE
BEGIN
SELECT TOP(@PageSize)
TE_MainInfo.ShopId,
TE_MainInfo.CreateDate AS SCreateDate,
TE_Domain.DomainName,
TE_Employe.FullName,
TE_Employe.Email,
TE_Certificate.CertCode,
TE_Certificate.RegistName,
TE_Certificate.CompanyName,
TE_Certificate.FileName,
TE_Certificate.Status,
TE_Certificate.CreateDate AS CCreateDate
FROM TE_MainInfo
LEFT JOIN TE_Domain ON TE_Domain.ShopId = TE_MainInfo.ShopId
AND TE_Domain.DomainType<>1
LEFT JOIN TE_Certificate ON TE_Certificate.ShopId = TE_MainInfo.ShopId
LEFT JOIN TR_ShopEmployee ON TR_ShopEmployee.ShopId = TE_MainInfo.ShopId
INNER JOIN TE_Employe ON TE_Employe.EmployeId = TR_ShopEmployee.EmployeId
AND TE_Employe.RoleId=100
WHERE (TE_Domain.DomainName LIKE '%' + ISNULL(@Value,TE_Domain.DomainName) + '%'
   OR TE_Employe.Email LIKE '%' + ISNULL(@Value,TE_Employe.Email) + '%'
   OR TE_Certificate.RegistName LIKE '%' + ISNULL(@Value,TE_Certificate.RegistName) + '%')
   AND ISNULL(TE_Certificate.Status,0)=ISNULL(@sqlStatus,ISNULL(TE_Certificate.Status,0))
AND TE_MainInfo.ShopId<(SELECT MIN(ShopId)
FROM(
SELECT TOP ((@PageIndex)*@PageSize) TE_MainInfo.ShopId
FROM TE_MainInfo
LEFT JOIN TE_Domain ON TE_Domain.ShopId = TE_MainInfo.ShopId
AND TE_Domain.DomainType<>1
LEFT JOIN TE_Certificate ON TE_Certificate.ShopId = TE_MainInfo.ShopId
LEFT JOIN TR_ShopEmployee ON TR_ShopEmployee.ShopId = TE_MainInfo.ShopId
INNER JOIN TE_Employe ON TE_Employe.EmployeId = TR_ShopEmployee.EmployeId
AND TE_Employe.RoleId=100
WHERE (TE_Domain.DomainName LIKE '%' + ISNULL(@Value,TE_Domain.DomainName) + '%'
OR TE_Employe.Email LIKE '%' + ISNULL(@Value,TE_Employe.Email) + '%'
OR TE_Certificate.RegistName LIKE '%' + ISNULL(@Value,TE_Certificate.RegistName) + '%')
AND ISNULL(TE_Certificate.Status,0)=ISNULL(@sqlStatus,ISNULL(TE_Certificate.Status,0))
ORDER BY TE_MainInfo.ShopId DESC)AS STEVEN)
ORDER BY SCreateDate DESC
END

6 个解决方案

#1

又没有什么嵌套只能靠加索引来提高查询效率了

#2

我测试过一些类似的查询，有时候order by的字段不同，效率也不同。但这个我试过了，却没什么用，我想是不是我的条件方面还可以优化下；比如关键字不用where，而是直接换成=号接在inner join后面。能不能帮我分析下，我知道这SQL比较长，难得看，大家帮帮我吧！

#3

只能靠加索引来提高查询效率了
where，而是直接换成=号接在inner join
效率没影响

#4

个人觉得,对该段程序查询速度影响最大的应该是这一段:

AND TE_MainInfo.ShopId<(

	SELECT MIN(ShopId) FROM(

		SELECT TOP ((@PageIndex)*@PageSize) TE_MainInfo.ShopId  

		FROM TE_MainInfo LEFT JOIN TE_Domain ON TE_Domain.ShopId = TE_MainInfo.ShopId AND TE_Domain.DomainType<>1

		LEFT JOIN TE_Certificate ON TE_Certificate.ShopId = TE_MainInfo.ShopId  

		LEFT JOIN TR_ShopEmployee ON TR_ShopEmployee.ShopId = TE_MainInfo.ShopId  

		INNER JOIN TE_Employe ON TE_Employe.EmployeId = TR_ShopEmployee.EmployeId AND TE_Employe.RoleId=100

		WHERE (TE_Domain.DomainName LIKE '%' + ISNULL(@Value,TE_Domain.DomainName) + '%'  

		OR TE_Employe.Email LIKE '%' + ISNULL(@Value,TE_Employe.Email) + '%'  

		OR TE_Certificate.RegistName LIKE '%' + ISNULL(@Value,TE_Certificate.RegistName) + '%')   

		AND ISNULL(TE_Certificate.Status,0)=ISNULL(@sqlStatus,ISNULL(TE_Certificate.Status,0))

		ORDER BY TE_MainInfo.ShopId DESC

	)AS STEVEN

)

建议先用一个数值来代替括号中的子查询测试一下,再看看有没有什么间接的办法获取这个MIN(ShopId)值,或许,用视图可能有所缓解吧.

#5

join 中的条件与 where 中的条件所产生的结果是不一样的,特别是在有左连接的时候,可能会使得在 join 中不被限制的记录在 where 语句结果中被限制掉的.

#6

视图、索引试过了，差不多少，视图的话，感觉没什么变化，索引也还是慢。
这个查询，我在页面加载的时候调用了4次，4个不同状态，查询4次。
MIN(ShopId)这个值怎么替代啊，它好像必须是从上面的查询中取出最小的一条吧，不然达不到分页效果了！
有没有可能是模糊搜索导致的?模糊搜索虽然有点影响查询效率，但我这边页面加载实在是慢的可怜啊，点击链接后估计要5到10秒才能加载完全！

#1