22,302
社区成员
发帖
与我相关
我的任务
分享求解~~~~~~~~SQL 存储过程执行结果参数改变后页面数据不变
ASP.Net 正常访问数据库，没有做任何缓存，从存储过程中返回List 绑定Reapter，有翻页。
在测试环境，预上线一直正常，线上之前也正常，最近一段时间就突然时不时的出现，数据一直保持第一页不变的情况。跟踪post返回状态正常，只是返回的数据后面也一直都是第一页。
存储过程不做任何改动，直接alter一下，就正常了。
这是为什么呢？求解~~~~~~~~
在测试环境，预上线一直正常，线上之前也正常，最近一段时间就突然时不时的出现，数据一直保持第一页不变的情况。跟踪post返回状态正常，只是返回的数据后面也一直都是第一页。
存储过程不做任何改动，直接alter一下，就正常了。
这是为什么呢？求解~~~~~~~~
...全文
请发表友善的回复…
发表回复
木房尘埃 2015-12-21
- 打赏
- 举报
谢谢您的耐性讲解，受益匪浅~~
[/quote]
以后会避免这种“奇怪”的方式
[/quote]
以后会避免这种“奇怪”的方式木房尘埃 2015-12-21
- 打赏
- 举报
木房尘埃 2015-12-21
- 打赏
- 举报
谢谢您的耐性讲解，受益匪浅~~
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
- 打赏
- 举报
非常感谢您的回复
等不到来世 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
- 打赏
- 举报
你有仔细看我给你写的版本吗？对比一下不就知道了
木房尘埃 2015-12-15
- 打赏
- 举报
好的，谢谢~~
道素 2015-12-14
- 打赏
- 举报
我没细看，所以就是随便说说，注意NOLOCK可能会读取到脏数据
木房尘埃 2015-12-11
- 打赏
- 举报
用top 的方式不是一样的效果吗？为什么说错的离谱呢？
木房尘埃 2015-12-10
- 打赏
- 举报
参数嗅探的解决方法是不是把变量本地化呢，这个最开始出现问题的时候把存储过程更新为变量本地化了，但是一段时候后还是这样的。[/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
- 打赏
- 举报
上线后这么没有过改动，正常使用有半年左右，只是最近数据量突然大了一点儿
因为把线上的数据库备份下来，在本地也没有问题，预上线也没有过这样的问题。
log或profiler 我没有权限，浏览器检测的post 的参数pageindex 是正确的。
LongRui888 2015-12-10
- 打赏
- 举报
参数嗅探的解决方法是不是把变量本地化呢，这个最开始出现问题的时候把存储过程更新为变量本地化了，但是一段时候后还是这样的。[/quote]
你这样，你打开sql server 的sql profiler 监控一下，然后把截获到的sql，执行一下，看看到底是什么问题导致的。
我觉得应该是你的存储过程本身写的有问题。
我觉得不太可能是参数嗅探，因为参数嗅探只是一种sql server 产生执行计划时候的策略而已，不会影响结果的正确性。
等不到来世 2015-12-10
- 打赏
- 举报
这不是个性能问题，是个功能问题！
我想知道，alter之后还出现总是显示第一页的情况吗？
猜测是上线之前你们又做了什么改动影响了分页。
可以用log或profiler等手段跟踪一下分页的参数,尤其是pageindex，看是不是想要的值。
木房尘埃 2015-12-10
- 打赏
- 举报
参数嗅探的解决方法是不是把变量本地化呢，这个最开始出现问题的时候把存储过程更新为变量本地化了，但是一段时候后还是这样的。
Yole 2015-12-10
- 打赏
- 举报
参数嗅探
木房尘埃 2015-12-10
- 打赏
- 举报
目前的解决方案确实是加了这个，但是这个存储过程用的都是常规参数，理论上应该不用这个的。
木房尘埃 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加载更多回复（4）
