Hi all,
I've got a total of 1.5 million records, with daily 100K new and 100K deleted that have expired.
I'm trying to display about 1.2 million of these records with pagination of a 25 at a time.
If I do that, it times out. I've added TOP 100, to at least have some result, but still takes too long to load 4 pages of 25 records each to display.
Here is how I open the record set:
Anyone any ideas to what could help speed this up a lot?
It is for display only, no editing needed at all.
I've got a total of 1.5 million records, with daily 100K new and 100K deleted that have expired.
I'm trying to display about 1.2 million of these records with pagination of a 25 at a time.
If I do that, it times out. I've added TOP 100, to at least have some result, but still takes too long to load 4 pages of 25 records each to display.
Here is how I open the record set:
Code:
SQL = "SELECT TOP 100 QuarID, EmailFrom, EmailTo, Subject, MsgDate, MsgID "
SQL = SQL & "FROM tblQuarantine "
SQL = SQL & "WHERE " & EmailToAddresses & " "
SQL = SQL & "ORDER BY " & order_by
set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open SQL , conn,adOpenKeyset,adLockOptimistic
if Request("nItemsPerPage") = "" then
If SummaryShowMaxPerPage <> "" then
nItemsPerPage = SummaryShowMaxPerPage
else
nItemsPerPage = 25
end if
else
nItemsPerPage = Request("nItemsPerPage")
end if
' Set the page size of the recordset
rs.PageSize = nItemsPerPage
' Get the number of pages
nPageCount = rs.PageCount
nPage = CLng(Request("nPage"))
If nPage < 1 Or nPage > nPageCount Then
nPage = 1
End If
If NOT rs.EOF and not rs.BOF then
rs.MoveFirst
rs.AbsolutePage = nPage
While Not (rs.EOF or rs.AbsolutePage <> nPage)
'.... displayed records go here
rs.MoveNext
Wend
Anyone any ideas to what could help speed this up a lot?
It is for display only, no editing needed at all.