不要select *不知道你的分页是怎么做的,不会是用 not in 吧?前天我写了一个access的 Public Function FormatSqlPager(ByVal StrWhere As String, ByVal PrimaryKey As String, ByVal QueryFields As String, ByVal TableName As String, ByVal PageSize As Integer, ByVal PageIndex As Integer, ByVal Counts As Integer) As String If StrWhere.Length > 0 Then StrWhere = " Where " & StrWhere End If Dim PageCount As Integer = CType(Math.Ceiling(CType(Counts, Double) / PageSize), Integer) Dim MiddleIndex As Integer = CType(Math.Ceiling(CType(PageCount, Double) / 2), Integer) - 1 Dim CountModer As Integer = (Counts Mod PageSize) '1 If PageIndex = 1 Then Return "SELECT * FROM (SELECT top " & PageSize & " " & QueryFields & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " DESC) TableA ORDER BY " & PrimaryKey & " DESC" '2~5 ElseIf PageIndex > 1 And PageIndex <= MiddleIndex Then Return "SELECT * FROM (SELECT top " & PageSize & " " & QueryFields & " FROM " & TableName & " WHERE " & PrimaryKey & " >=(SELECT MIN(" & PrimaryKey & ") FROM (SELECT TOP " & PageSize * PageIndex & " " & PrimaryKey & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " DESC))) ORDER BY " & PrimaryKey & " DESC" '6~10 ElseIf PageIndex > MiddleIndex And PageIndex < PageCount Then Return "SELECT top " & PageSize & " * FROM (SELECT TOP " & Counts - (PageIndex - 1) * PageSize & " " & QueryFields & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " ASC) ORDER BY " & PrimaryKey & " DESC" '11 ElseIf PageIndex >= PageCount Then Return "SELECT * FROM (SELECT top " & CountModer & " " & QueryFields & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " ASC) ORDER BY " & PrimaryKey & " DESC" End If End Function Dim PrimaryKey As String = "T_BookInfo.Book_ID" Dim TableName As String = "((T_BookInfo INNER JOIN T_Book_Class_Child ON T_BookInfo.ChildID = T_Book_Class_Child.ChildID) INNER JOIN T_Book_Publisher ON T_BookInfo.Publisher_ID = T_Book_Publisher.Publisher_ID)" Dim QueryFields As String = "T_BookInfo.Book_ID, T_BookInfo.Book_Name,T_BookInfo.Book_ImageURl, T_BookInfo.Book_Author, T_BookInfo.Book_Price, T_BookInfo.Book_Discount, T_BookInfo.BrowseCount, T_BookInfo.BuyCount, T_BookInfo.Recommendation,T_BookInfo.Book_ISBN, T_Book_Class_Child.ChildName, T_Book_Publisher.Publisher_Name" Dim SelectRintDa As New 图书管理 Dim PageSize As Integer = 40 Dim RowCount As Integer = SelectRintDa.FormatPagerCount(Me.GetWhere, PrimaryKey, TableName)
Dim Err As String = "" Me.DataGrid1.DataSource = SelectRintDa.查询图书列表(FormatSqlPager(Me.GetWhere, PrimaryKey, QueryFields, TableName, PageSize, i, RowCount))
To:noyester ,第一页没有用not in 其他的用的是not in
1.建立恰当的索引; 2.尽量不用select * from tab_name,根据用多少取多少的原则
在130多万条中就有一条符合条件的,在网页的datagrid里面呈现出来,你们大约需要多长时间
http://www.channel7.cn/2004/11-29/201850.html not in效率是非常低的。只要把符合条件的记录查询出来,然后倒序,然后再取出要显示的那一段数据就可以,下一页的时候,继续top xx取
我也在处理上亿条记录的数据库,楼主的表建相关索引了吗?
还有select语句不必读取所有字段,选择相关字段即可,速度会快很多
StrWhere = " Where " & StrWhere
End If
Dim PageCount As Integer = CType(Math.Ceiling(CType(Counts, Double) / PageSize), Integer)
Dim MiddleIndex As Integer = CType(Math.Ceiling(CType(PageCount, Double) / 2), Integer) - 1
Dim CountModer As Integer = (Counts Mod PageSize)
'1
If PageIndex = 1 Then
Return "SELECT * FROM (SELECT top " & PageSize & " " & QueryFields & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " DESC) TableA ORDER BY " & PrimaryKey & " DESC"
'2~5
ElseIf PageIndex > 1 And PageIndex <= MiddleIndex Then Return "SELECT * FROM (SELECT top " & PageSize & " " & QueryFields & " FROM " & TableName & " WHERE " & PrimaryKey & " >=(SELECT MIN(" & PrimaryKey & ") FROM (SELECT TOP " & PageSize * PageIndex & " " & PrimaryKey & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " DESC))) ORDER BY " & PrimaryKey & " DESC"
'6~10
ElseIf PageIndex > MiddleIndex And PageIndex < PageCount Then Return "SELECT top " & PageSize & " * FROM (SELECT TOP " & Counts - (PageIndex - 1) * PageSize & " " & QueryFields & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " ASC) ORDER BY " & PrimaryKey & " DESC"
'11
ElseIf PageIndex >= PageCount Then Return "SELECT * FROM (SELECT top " & CountModer & " " & QueryFields & " FROM " & TableName & " " & StrWhere & " ORDER BY " & PrimaryKey & " ASC) ORDER BY " & PrimaryKey & " DESC"
End If
End Function
Dim PrimaryKey As String = "T_BookInfo.Book_ID"
Dim TableName As String = "((T_BookInfo INNER JOIN T_Book_Class_Child ON T_BookInfo.ChildID = T_Book_Class_Child.ChildID) INNER JOIN T_Book_Publisher ON T_BookInfo.Publisher_ID = T_Book_Publisher.Publisher_ID)"
Dim QueryFields As String = "T_BookInfo.Book_ID, T_BookInfo.Book_Name,T_BookInfo.Book_ImageURl, T_BookInfo.Book_Author, T_BookInfo.Book_Price, T_BookInfo.Book_Discount, T_BookInfo.BrowseCount, T_BookInfo.BuyCount, T_BookInfo.Recommendation,T_BookInfo.Book_ISBN, T_Book_Class_Child.ChildName, T_Book_Publisher.Publisher_Name" Dim SelectRintDa As New 图书管理
Dim PageSize As Integer = 40
Dim RowCount As Integer = SelectRintDa.FormatPagerCount(Me.GetWhere, PrimaryKey, TableName)
Dim Err As String = ""
Me.DataGrid1.DataSource = SelectRintDa.查询图书列表(FormatSqlPager(Me.GetWhere, PrimaryKey, QueryFields, TableName, PageSize, i, RowCount))
2.尽量不用select * from tab_name,根据用多少取多少的原则
not in效率是非常低的。只要把符合条件的记录查询出来,然后倒序,然后再取出要显示的那一段数据就可以,下一页的时候,继续top xx取