SQL - not in 和 join

  今天做了7W行的数据分页 存储过程如下：

 

ALTER PROCEDURE  [dbo].[proc_select_page_top]
@pageindex INT=1,		--当前页数
@pagesize INT=5,		--每页大小
@tablename VARCHAR(1000)='',	--表名
@fields VARCHAR(1000)='',	--查询的字段集合
@keyid VARCHAR(50)='',		--主键
@condition NVARCHAR(1000)='',		--查询条件
@orderstr VARCHAR(500),			--排序条件
@totalRecord BIGINT OUTPUT		--总记录数
AS
    begin 
	IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
	IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
	IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
	DECLARE @sql NVARCHAR(4000)
	SET @sql=N'SELECT @totalRecord=COUNT(*)'
		+N' FROM '+@tablename
		+N' WHERE '+@condition
	EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT

	SET NOCOUNT ON  


	IF(@pageindex=1)
	BEGIN
		SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
			EXEC(@sql)
	END
	ELSE
	BEGIN
		SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE  '+@keyid
		+N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '
		+@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr
		print @sql
		--EXEC(@sql)
	END

END

每页20条读取，开始运行起来感觉很快，但是越到后面感觉原来越慢甚至到最后页第365页，用了11秒钟去只读取，感觉性能出现了很大的问题，最后发现在not in 这里产生了瓶颈：

   

+N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '

找了很多替代的参考，都说left join  比较省事于是改测试改写成:

     原来的sql:

     

SELECT TOP   20  *  FROM  vw_EmployeeLeaves 
					WHERE  applicationid NOT IN(
					SELECT TOP 7280 applicationid 
					FROM  vw_EmployeeLeaves  
					WHERE  1 = 1 AND EmployeeStatus = 'Effective'   
					ORDER BY applicationID DESC ) AND  1 = 1 AND EmployeeStatus = 'Effective' 
					ORDER BY applicationID DESC 

 现在的SQL:

  

   SELECT TOP 20 A.* FROM  vw_EmployeeLeaves A LEFT join (
					 select top  7280 applicationid 
					 FROM  vw_EmployeeLeaves  
					 WHERE  1 = 1 AND EmployeeStatus = 'Effective'  
					 ORDER BY applicationID DESC)  as B
					on A.applicationID=B.applicationid     
					WHERE  1 = 1 AND b.applicationID is null  AND A.EmployeeStatus = 'Effective'   
					ORDER BY A.applicationID DESC

执行结果分别为11秒，120毫秒

descr		 execDate
NotIn --start	2013-12-18 15:47:41.680
NotIn --end	2013-12-18 15:47:52.730
Join start	2013-12-18 15:47:52.730
Join End	         2013-12-18 15:47:52.850

 
到此为止比较觉得用比较面议的JOIN 代替 NOT IN ,但是在准备代替JOIN前发现了为什么not in 效率很低的问题，问题出现在：

  ORDER BY A.applicationID 

applicationID 是GUID类型的，为此换成了字符 applicaitonCODE

 

执行结果分别为57毫秒，124毫秒

descr		execDate
NotIn --start	2013-12-18 15:53:25.260
NotIn --end	2013-12-18 15:53:25.323
Join start	2013-12-18 15:53:25.323
Join End	         2013-12-18 15:53:25.447

not in 和join 取舍大家自己做参考了