分页查询超时问题(1)

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">晚上割接了1500万联系人账号到EI_LinkmanUserInfos表，第二天程序报告下面的查询语句超时</span>

;WITH a AS (
SELECT ROW_NUMBER() OVER(ORDER BY l.LinkmanId) AS LinkmanCount,
l.LinkmanId,l.Name,l.LinkmanType
,l.Mobile,l.Email,l.DepartmentId,d.Name AS DepartmentName,l.Position,l.Code AS VnetCode  
FROM dbo.EI_Linkmen l 
     LEFT JOIN dbo.EI_Departments d ON l.DepartmentId=d.DepartmentId  
     left join (SELECT * FROM  (
								  SELECT ROW_NUMBER() OVER(PARTITION BY LinkmanId ORDER BY LinkmanUserInfoId) AS rn,*  
								  FROM dbo.EI_LinkmanUserInfos ) uix WHERE uix.rn=1) ui on l.LinkmanId=ui.linkmanid   
								  WHERE l.DataStatus = 0 AND l.EnterpriseId=916627  ) 
  SELECT (SELECT COUNT(1) FROM a ) AS TotalCount,LinkmanId,Name,Mobile,Email,DepartmentId,DepartmentName,LinkmanType
   as [Type],Position,OpenContent='',VnetCode  
FROM a WHERE LinkmanCount BETWEEN 1 AND 10

分析：

1、查询中左连接了EI_LinkmanUserInfos表，但是查询的结果列表中没有包含EI_LinkmanUserInfos表的任何字段。

2、where条件中只包含对表EI_LinkMen表的过虑l.DataStatus = 0 AND l.EnterpriseId=916627,并且EI_Departments.departmentID是主键，这种情况下应该先对EI_LinkMen分页，再去关联EI_Departments。

SQL语句改成先分页，再查询关联信息：

;with a as(
select ROW_NUMBER() OVER(ORDER BY l.LinkmanId) AS LinkmanCount, l.LinkmanId,l.Name,l.LinkmanType
       ,l.Mobile,l.Email,l.DepartmentId,l.Position,l.Code AS VnetCode  
from  ei_linkmen l
where DataStatus=0 and EnterpriseId=916627
)
select (SELECT COUNT(1) FROM a ) AS TotalCount,l.LinkmanId,l.Name,Mobile,Email,l.DepartmentId
	   ,d.Name DepartmentName,LinkmanType,x.AccountName,x.AccountECcode
from a l
     LEFT JOIN dbo.EI_Departments d ON l.DepartmentId=d.DepartmentId 
     outer apply(select top 1 * from EI_LinkmanUserInfos where LinkmanId=l.LinkmanId order by LinkmanUserInfoId) x
where  LinkmanCount BETWEEN 1 AND 10