探讨关于ASP.NET翻页的性能问题
大家都知道，DataSet消耗系统资源很大，那用SqlDataReader来处理是否会好一些呢？
我先用SqlDataReader查询需要显示的数据的ID集合 如：strTemp_id= "2,3,4,6,8,12,24,56,57,78 "
并把该值赋值给页面控件Temp_id,以便翻页时不再查询
再在strTemp_id中根据每页显示的数量和本次是显示页次在来判断出本页显示的Id几个str_Id
最后用SqlDataReader查询ID值包含在str_Id中的数据并绑定
' ' ' ' ' ' ' ' ' ' ' ' ' ' 'Repeater.aspx ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
<asp:Repeater ID= "My_Rep " runat= "server ">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem, "Job_id ")%>
<%#DataBinder.Eval(Container.DataItem, "Job_desc ")%>
</ItemTemplate>
</asp:Repeater>
<asp:Label ID= "PageButton_Lab " runat= "server "> </asp:Label>
<form name= "myForm " method= "post ">
<input id= "Temp_id " type= "hidden " name= "Temp_id " runat= "server ">
<script language= "javascript ">
function PageToUrl(url)
{
document.myForm.action=url;
document.myForm.submit();
}
</script>
</form>
' ' ' ' ' ' ' ' ' 'Repeater.aspx.vb ' ' ' ' ' ' ' ' ' ' '
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
My_Rep_DataBind()
End If
End Sub
Sub My_Rep_DataBind()
Dim strTemp_id As String = Request.Form( "Temp_id ") '该变量用来判断是否是第一次显示页面
If strTemp_id = " " Then '如果为空即为第一次查询
str_Sql = "select job_id from jobs where job_Id> 0 " & str_ssSql & " order by job_id "
Dim Rs As SqlDataReader = GetSdr(str_Sql)
While (Rs.Read())
strTemp_id = strTemp_id & ", " & Rs(0).ToString()
End While
Close() '关闭数据库
Rs.Close()
strTemp_id = Mid(strTemp_id, 2) '返回的str_Sql执行后的一组ID值
End If
Temp_id.Value = strTemp_id
Dim int_Dsc As Integer '总记录数
Dim int_Pc As Integer '总页数
Dim int_Cpi As Integer '当前页数
Dim int_Ps As Integer = 5 '每页显示条数
int_Dsc = strTemp_id.Split( ", ").Length
If (int_Dsc Mod int_Ps) <> 0 Then
int_Pc = int_Pc + 1
Else
int_Pc = int_Dsc \ int_Ps
End If
If Request.QueryString( "Page ") <> " " Then
int_Cpi = Request.QueryString( "Page ")
Else
int_Cpi = 1
End If
Dim str_Id As String = " " '当前页的Id集合
Dim i As Integer
For i = (int_Cpi - 1) * int_Ps To (int_Cpi * int_Ps) - 1
If i > = int_Dsc Then
Exit For
End If
str_Id = str_Id & ", " & strTemp_id.Split( ", ")(i)
Next
str_Id = Mid(str_Id, 2)
str_Sql = "select job_id,job_desc from jobs where Job_Id in ( " & str_Id & ") order by job_id "
My_Rep.DataSource = Cf_Data.GetSdr(str_Sql) '使用SqlDataReader进行绑定
My_Rep.DataBind()
Cf_Data.Close() '关闭数据库
Page_Bind(int_Dsc, int_Pc, int_Ps, int_Cpi)
End Sub
Sub Page_Bind(ByVal int_Dsc As Integer, ByVal int_Pc As Integer, ByVal int_Ps As Integer, ByVal int_Cpi As Integer)
'int_Dsc总记录数 int_Pc总页数 int_Ps每页显示条数 int_Cpi当前页
Dim Page_Sb As New StringBuilder
Page_Sb.Append( "页次： <b> " & int_Cpi.ToString() & " </b> / <b> " & int_Pc.ToString() & " </b> 页 每页： <b> " & int_Ps.ToString() & " </b> 条 记录数： <b> " & int_Dsc.ToString() & " </b> ")
Page_Sb.Append( " 页次： ")
If int_Cpi > 10 Then
'Page_Sb.Append( " <a title=首页 href= " "javascript:PageToUrl( '?page=1 " & str_ssUrl & " ') " "> <font face=webdings> 9 </font> </a> | ")
Page_Sb.Append( " <a title= " "上十页 " " href= " "javascript:PageToUrl( '?page= " & (int_Cpi - 10).ToString() & str_ssUrl & " ') " "> <font face=webdings> 7 </font> </a> | ")
End If
Dim p0, p1, p2 As Integer
p0 = (int_Cpi - 1) \ 10
p1 = p0 * 10 + 1
p2 = p0 * 10 + 10
If p2 > int_Pc Then
p2 = int_Pc
End If
Dim i As Integer
For i = p1 To p2
If i = int_Cpi Then
Page_Sb.Append( " <font color=#FF0000> " & i.ToString() & " </font> | ")
Else
Page_Sb.Append( " <a title= " "第 " & i.ToString() & "页 " " href= " "javascript:PageToUrl( '?page= " & i.ToString() & str_ssUrl & " ') " "> " & i.ToString() & " </a> | ")
'Page_Sb.Append( " <a title=最后一页 href= '?page= " & i.ToString() & str_ssUrl & " '> [ <strong> " & i.ToString() & " </strong> ] </a> ")
End If
Next
If int_Pc > p2 Then
Page_Sb.Append( " <a title= " "下十页 " " href= " "javascript:PageToUrl( '?page= " & (int_Cpi + 10).ToString() & str_ssUrl & " ') " "> <font face=webdings> 8 </font> </a> | ")
Page_Sb.Append( " <a title= " "尾页 " " href= " "javascript:PageToUrl( '?page= " & int_Pc.ToString() & str_ssUrl & " ') " "> <font face=webdings> : </font> </a> | ")
End If
PageButton_Lab.Text = Page_Sb.ToString()
End Sub
[解决办法]
换 sql server 2005 吧.....
--------------------------------------------
http://www.parsesoft.net
致力于 DocBook 的应用，专注于开源软件的解析、手册/指南、以及应用文章、书籍的发布。
[解决办法]
不一定要存储过程分页....程序里用sql语句不页一样分页么...
[解决办法]
你这样的分页一点优点也体现不出来。
存储过程，通俗的讲对SQL语句的封装编译，以降低太多IO数据，和编译时间
你要搞清楚，分页是取总数和当前页的数据。而你取出来ID集合，去取等于在外部多加数据，你自己可以测试50W条以上的数据来试。
