求解~~~~~~~~SQL 存储过程执行结果参数改变后页面数据不变

木房尘埃 2015-12-09 04:45:14
ASP.Net 正常访问数据库,没有做任何缓存,从存储过程中返回List 绑定Reapter,有翻页。

在测试环境,预上线一直正常,线上之前也正常,最近一段时间就突然时不时的出现,数据一直保持第一页不变的情况。跟踪post返回状态正常,只是返回的数据后面也一直都是第一页。

存储过程不做任何改动,直接alter一下,就正常了。

这是为什么呢?求解~~~~~~~~
...全文
248 点赞 收藏 24
写回复
24 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
木房尘埃 2015-12-21
引用 22 楼 mine3333 的回复:
[quote=引用 20 楼 szx1999 的回复:] "我的写法是找出大于的序号,直接取前top 条" 你这么做,能得到正确结果的前提是,结果集已经严格按生成的序号排好了顺序。否则你怎么知道这top 10条一定是连续的从21-30, 而不会夹杂序号为35,45的记录? 数据量,数据分布等因素都会影响最终使用何种执行计划,而不同的执行计划又会影响一个临时结果集的默认排序,你top出来的结果其实是存在不确定因素的。而使用with recompile强制每次生成新的执行计划,从一定程度上减小了这种不确定因素,但不是根本解决之道。 基于你的“奇怪”的存储过程,我的建议是,末尾加上 "order by RowNumber", 这样基本就没啥问题了。
谢谢您的耐性讲解,受益匪浅~~[/quote] 以后会避免这种“奇怪”的方式
回复
木房尘埃 2015-12-21
引用 21 楼 Tiger_Zhao 的回复:
赞同 szx1999,和记录次序有关的查询(比如分页)必须要有 ORDER BY,要么是 SELECT TOP ... ORDER BY 格式,要么是在 ROW_NUMBER() OVER(ORDER BY) 格式。 数据库查询结果次序具有不定性,这是基本概念。
回复
木房尘埃 2015-12-21
引用 20 楼 szx1999 的回复:
"我的写法是找出大于的序号,直接取前top 条" 你这么做,能得到正确结果的前提是,结果集已经严格按生成的序号排好了顺序。否则你怎么知道这top 10条一定是连续的从21-30, 而不会夹杂序号为35,45的记录? 数据量,数据分布等因素都会影响最终使用何种执行计划,而不同的执行计划又会影响一个临时结果集的默认排序,你top出来的结果其实是存在不确定因素的。而使用with recompile强制每次生成新的执行计划,从一定程度上减小了这种不确定因素,但不是根本解决之道。 基于你的“奇怪”的存储过程,我的建议是,末尾加上 "order by RowNumber", 这样基本就没啥问题了。
谢谢您的耐性讲解,受益匪浅~~
回复
Tiger_Zhao 2015-12-18
赞同 szx1999,和记录次序有关的查询(比如分页)必须要有 ORDER BY,要么是 SELECT TOP ... ORDER BY 格式,要么是在 ROW_NUMBER() OVER(ORDER BY) 格式。
数据库查询结果次序具有不定性,这是基本概念。
回复
等不到来世 2015-12-18
"我的写法是找出大于的序号,直接取前top 条" 你这么做,能得到正确结果的前提是,结果集已经严格按生成的序号排好了顺序。否则你怎么知道这top 10条一定是连续的从21-30, 而不会夹杂序号为35,45的记录? 数据量,数据分布等因素都会影响最终使用何种执行计划,而不同的执行计划又会影响一个临时结果集的默认排序,你top出来的结果其实是存在不确定因素的。而使用with recompile强制每次生成新的执行计划,从一定程度上减小了这种不确定因素,但不是根本解决之道。 基于你的“奇怪”的存储过程,我的建议是,末尾加上 "order by RowNumber", 这样基本就没啥问题了。
回复
木房尘埃 2015-12-18
引用 18 楼 szx1999 的回复:
你的存储过程有如下问题: 1. 参数已经本地化了,再使用with recompile是画蛇添足。 这个存储过程在系统中已经正常使用半年了,最近是又稍微大点的数据量进来。 因为数据出来的蹊跷,所以怀疑是缓存的问题,加了本地化一段时间又出现了问题,所以加的 with recompile,暂时还未出现问题。 正常情况这两个确实都是不需要的 2. @TotalCount 取值错误。如果RH_Group和另外三个表有1对多的关系,那么@TotalCount肯定比实际条数要少。 left join 的表都是一对一的, 3. 取指定页数据错误。sql2005以前的分页,是用双top法,形如select top 10 * from (select top 20 * from tb order by id desc) order by id,具体用法可以再取百度一下;sql2005及以后的分页,通常用rownumber+between/and法,形如select * from (select *,rn=row_number over(order by id) from tb) where rn between 10 and 20。 而你的写法,一方面不伦不类,既然已经用了rownumber,为什么又用top?这样把rownumber函数大材小用了;另一方面,你使用了top, 却不指定相应的order by子句,这样top出来的结果未必是想要的,因为没有order by,中间结果的顺序通常是不可预知的。尽管有rownumber的语句通常是按其内部orderby来排序,但外层有嵌套,语句复杂之后,顺序就变得难以固定了。 我的写法是找出大于的序号,直接取前top 条,与您的between 效果类似。因为用的是 行号过滤的,所以排序对数据本身并不影响 4. Row_number函数使用错误。Row_number必须使用在所有表连接之后的总结果集上才有作用,你把它用在了最里面的单表结果上,这对外层结果的排序毫无意义。 这个与第三条类似,外层是通过行号过滤出来正确顺序的数据了
非常感谢您的回复
回复
等不到来世 2015-12-15
你的存储过程有如下问题: 1. 参数已经本地化了,再使用with recompile是画蛇添足。 2. @TotalCount 取值错误。如果RH_Group和另外三个表有1对多的关系,那么@TotalCount肯定比实际条数要少。 3. 取指定页数据错误。sql2005以前的分页,是用双top法,形如select top 10 * from (select top 20 * from tb order by id desc) order by id,具体用法可以再取百度一下;sql2005及以后的分页,通常用rownumber+between/and法,形如select * from (select *,rn=row_number over(order by id) from tb) where rn between 10 and 20。 而你的写法,一方面不伦不类,既然已经用了rownumber,为什么又用top?这样把rownumber函数大材小用了;另一方面,你使用了top, 却不指定相应的order by子句,这样top出来的结果未必是想要的,因为没有order by,中间结果的顺序通常是不可预知的。尽管有rownumber的语句通常是按其内部orderby来排序,但外层有嵌套,语句复杂之后,顺序就变得难以固定了。 4. Row_number函数使用错误。Row_number必须使用在所有表连接之后的总结果集上才有作用,你把它用在了最里面的单表结果上,这对外层结果的排序毫无意义。
回复
等不到来世 2015-12-15
引用 14 楼 mine3333 的回复:
引用 13 楼 szx1999 的回复:
GO[/code]
用top 的方式不是一样的效果吗?为什么说错的离谱呢?
你有仔细看我给你写的版本吗?对比一下不就知道了
回复
木房尘埃 2015-12-15
引用 15 楼 ch21st 的回复:
我没细看,所以就是随便说说,注意NOLOCK可能会读取到脏数据
好的,谢谢~~
回复
道素 2015-12-14
我没细看,所以就是随便说说,注意NOLOCK可能会读取到脏数据
回复
木房尘埃 2015-12-11
引用 13 楼 szx1999 的回复:
if OBJECT_ID('TR_Group_GetGroupCategoryList','P') is not null
	drop proc TR_Group_GetGroupCategoryList
GO
create proc TR_Group_GetGroupCategoryList
	@CID INT ,
	@GroupSortID INT ,
	@PageSize INT ,
	@PageIndex INT ,
	@TotalCount INT OUTPUT
as
DECLARE @varCID INT,@varGroupSortID INT,@startIndex INT,@endIndex INT
SELECT @varCID = @CID,@varGroupSortID = @GroupSortID
SET @startIndex=@PageSize*@PageIndex+1
SET @endIndex=@startIndex+@PageSize-1

SELECT  @TotalCount = COUNT(1)
FROM    dbo.RH_Group a WITH ( NOLOCK )
LEFT JOIN dbo.RH_User u WITH ( NOLOCK ) ON u.id = a.CreateBy
LEFT JOIN dbo.RH_Subject s WITH ( NOLOCK ) ON s.ID = a.SubjectID
LEFT JOIN dbo.RH_Grade d WITH ( NOLOCK ) ON d.ID = a.GradeID
WHERE   a.CID = @varCID
AND a.GroupStatus = 2
AND a.GroupSortID = @varGroupSortID

SELECT * 
FROM
(
	SELECT a.ID ,a.GroupName ,a.GroupSortID ,a.GradeID ,a.SubjectID ,a.GPictureUrl 
		,a.GroupDesc ,a.CreateBy ,a.CreateTime 
		,(SELECT COUNT(UserID) FROM dbo.RH_GroupUser WITH ( NOLOCK )
          WHERE GroupID = a.id AND GroupUserState = 2) AS GroupUserCount
        ,u.TureName AS CreateByName ,s.SubjectName ,d.Name AS GradeName 
        ,(SELECT COUNT(1) FROM dbo.RH_Activity WITH ( NOLOCK )
          WHERE GroupID = a.id) AS ActivityCount
        ,ROW_NUMBER() OVER(ORDER BY a.CreateTime DESC) RowNumber 
	FROM dbo.RH_Group a WITH ( NOLOCK )
	LEFT JOIN dbo.RH_User u WITH ( NOLOCK ) ON u.id = a.CreateBy
	LEFT JOIN dbo.RH_Subject s WITH ( NOLOCK ) ON s.ID = a.SubjectID
	LEFT JOIN dbo.RH_Grade d WITH ( NOLOCK ) ON d.ID = a.GradeID
	WHERE   a.CID = @varCID
	AND a.GroupStatus = 2
	AND a.GroupSortID = @varGroupSortID
) t
WHERE RowNumber BETWEEN @startIndex AND @endIndex
GO
用top 的方式不是一样的效果吗?为什么说错的离谱呢?
回复
木房尘埃 2015-12-10
引用 7 楼 yupeigu 的回复:
[quote=引用 5 楼 mine3333 的回复:] [quote=引用 4 楼 u010192842 的回复:] 参数嗅探
参数嗅探的解决方法是不是把变量本地化呢,这个最开始出现问题的时候把存储过程更新为变量本地化了,但是一段时候后还是这样的。[/quote] 你这样,你打开sql server 的sql profiler 监控一下,然后把截获到的sql,执行一下,看看到底是什么问题导致的。 我觉得应该是你的存储过程本身写的有问题。 我觉得不太可能是参数嗅探,因为参数嗅探只是一种sql server 产生执行计划时候的策略而已,不会影响结果的正确性。[/quote] 这个是我的存储过程,请帮忙看看,谢谢~ WITH RECOMPILE 是昨天才加的 SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO ALTER PROC [dbo].[TR_Group_GetGroupCategoryList] ( @CID INT , @GroupSortID INT , @PageSize INT , @PageIndex INT , @TotalCount INT OUTPUT ) WITH RECOMPILE AS BEGIN DECLARE @varCID INT DECLARE @varGroupSortID INT DECLARE @varPageSize INT DECLARE @varPageIndex INT SET @varCID = @CID SET @varGroupSortID = @GroupSortID SET @varPageSize = @PageSize SET @varPageIndex = @PageIndex SELECT @TotalCount = COUNT(1) FROM dbo.RH_Group WITH ( NOLOCK ) WHERE CID = @varCID AND GroupStatus = 2 AND GroupSortID = @varGroupSortID SELECT TOP ( @varPageSize ) A.* , ( SELECT COUNT(UserID) FROM dbo.RH_GroupUser WITH ( NOLOCK ) WHERE GroupID = a.id AND GroupUserState = 2 ) AS GroupUserCount , u.TureName AS CreateByName , s.SubjectName , d.Name AS GradeName , ( SELECT COUNT(1) FROM dbo.RH_Activity WITH ( NOLOCK ) WHERE GroupID = A.id ) AS ActivityCount FROM ( SELECT ID , GroupName , GroupSortID , GradeID , SubjectID , GPictureUrl , GroupDesc , CreateBy , CreateTime , ROW_NUMBER() OVER ( ORDER BY CreateTime DESC ) RowNumber FROM dbo.RH_Group g WITH ( NOLOCK ) WHERE CID = @varCID AND GroupStatus = 2 AND GroupSortID = @varGroupSortID ) A LEFT JOIN dbo.RH_User u WITH ( NOLOCK ) ON u.id = a.CreateBy LEFT JOIN dbo.RH_Subject s WITH ( NOLOCK ) ON s.ID = a.SubjectID LEFT JOIN dbo.RH_Grade d WITH ( NOLOCK ) ON d.ID = a.GradeID WHERE RowNumber > ( @varPageSize * @varPageIndex ) END GO
回复
木房尘埃 2015-12-10
引用 6 楼 szx1999 的回复:
这不是个性能问题,是个功能问题! 我想知道,alter之后还出现总是显示第一页的情况吗? 猜测是上线之前你们又做了什么改动影响了分页。 可以用log或profiler等手段跟踪一下分页的参数,尤其是pageindex,看是不是想要的值。
上线后这么没有过改动,正常使用有半年左右,只是最近数据量突然大了一点儿 因为把线上的数据库备份下来,在本地也没有问题,预上线也没有过这样的问题。 log或profiler 我没有权限,浏览器检测的post 的参数pageindex 是正确的。
回复
引用 5 楼 mine3333 的回复:
[quote=引用 4 楼 u010192842 的回复:] 参数嗅探
参数嗅探的解决方法是不是把变量本地化呢,这个最开始出现问题的时候把存储过程更新为变量本地化了,但是一段时候后还是这样的。[/quote] 你这样,你打开sql server 的sql profiler 监控一下,然后把截获到的sql,执行一下,看看到底是什么问题导致的。 我觉得应该是你的存储过程本身写的有问题。 我觉得不太可能是参数嗅探,因为参数嗅探只是一种sql server 产生执行计划时候的策略而已,不会影响结果的正确性。
回复
等不到来世 2015-12-10
这不是个性能问题,是个功能问题! 我想知道,alter之后还出现总是显示第一页的情况吗? 猜测是上线之前你们又做了什么改动影响了分页。 可以用log或profiler等手段跟踪一下分页的参数,尤其是pageindex,看是不是想要的值。
回复
木房尘埃 2015-12-10
引用 4 楼 u010192842 的回复:
参数嗅探
参数嗅探的解决方法是不是把变量本地化呢,这个最开始出现问题的时候把存储过程更新为变量本地化了,但是一段时候后还是这样的。
回复
Yole 2015-12-10
参数嗅探
回复
木房尘埃 2015-12-10
引用 1 楼 moudy 的回复:
存储过程加个WITH RECOMPILE 试试 ex: Create Procedure XXXX WITH RECOMPILE AS .................. Go
目前的解决方案确实是加了这个,但是这个存储过程用的都是常规参数,理论上应该不用这个的。
回复
木房尘埃 2015-12-10
目前的解决方案确实是加了这个,但是这个存储过程用的都是常规参数,理论上应该不用这个的。
回复
等不到来世 2015-12-10
if OBJECT_ID('TR_Group_GetGroupCategoryList','P') is not null
	drop proc TR_Group_GetGroupCategoryList
GO
create proc TR_Group_GetGroupCategoryList
	@CID INT ,
	@GroupSortID INT ,
	@PageSize INT ,
	@PageIndex INT ,
	@TotalCount INT OUTPUT
as
DECLARE @varCID INT,@varGroupSortID INT,@startIndex INT,@endIndex INT
SELECT @varCID = @CID,@varGroupSortID = @GroupSortID
SET @startIndex=@PageSize*@PageIndex+1
SET @endIndex=@startIndex+@PageSize-1

SELECT  @TotalCount = COUNT(1)
FROM    dbo.RH_Group a WITH ( NOLOCK )
LEFT JOIN dbo.RH_User u WITH ( NOLOCK ) ON u.id = a.CreateBy
LEFT JOIN dbo.RH_Subject s WITH ( NOLOCK ) ON s.ID = a.SubjectID
LEFT JOIN dbo.RH_Grade d WITH ( NOLOCK ) ON d.ID = a.GradeID
WHERE   a.CID = @varCID
AND a.GroupStatus = 2
AND a.GroupSortID = @varGroupSortID

SELECT * 
FROM
(
	SELECT a.ID ,a.GroupName ,a.GroupSortID ,a.GradeID ,a.SubjectID ,a.GPictureUrl 
		,a.GroupDesc ,a.CreateBy ,a.CreateTime 
		,(SELECT COUNT(UserID) FROM dbo.RH_GroupUser WITH ( NOLOCK )
          WHERE GroupID = a.id AND GroupUserState = 2) AS GroupUserCount
        ,u.TureName AS CreateByName ,s.SubjectName ,d.Name AS GradeName 
        ,(SELECT COUNT(1) FROM dbo.RH_Activity WITH ( NOLOCK )
          WHERE GroupID = a.id) AS ActivityCount
        ,ROW_NUMBER() OVER(ORDER BY a.CreateTime DESC) RowNumber 
	FROM dbo.RH_Group a WITH ( NOLOCK )
	LEFT JOIN dbo.RH_User u WITH ( NOLOCK ) ON u.id = a.CreateBy
	LEFT JOIN dbo.RH_Subject s WITH ( NOLOCK ) ON s.ID = a.SubjectID
	LEFT JOIN dbo.RH_Grade d WITH ( NOLOCK ) ON d.ID = a.GradeID
	WHERE   a.CID = @varCID
	AND a.GroupStatus = 2
	AND a.GroupSortID = @varGroupSortID
) t
WHERE RowNumber BETWEEN @startIndex AND @endIndex
GO
回复
加载更多回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-12-09 04:45
社区公告
暂无公告