pagination of 1.2 million records takes too long 2

[POSAPAY]

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:

	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.

 

[gmmastros]

See... that's the thing... I don't use full text indexing, so I can't really answer your question. Sorry. I do know that this is precisely the problem that full text indexing was invented to solve, which is why I am recommending you research it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[POSAPAY]

Got it.... I'll report back with my findings. Thanks for the Tip!

 

[POSAPAY]

Got one article in case anyone else needs it or is researching it:

[URL unfurl="true"]http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm[/url]

You might be a bit surprised to learn that SQL Server doesn't handle its own full-text indexing tasks. Any version of Windows that SQL Server will run on includes an operating system component named the Microsoft Search Service. This service provides indexing and searching capabilities to a variety of applications, including SQL Server, Exchange, and SharePoint.

Then again, in SQL 2005:
SQL Server 2005 features quite a number of changes and improvements in full-text searching:

A dedicated indexing service that works directly with SQL Serrver. This speeds up full-text operations and isolates SQL Server from changes to the search service made by other applications.
Data definition language (DDL) statements for creating and altering full-text catalogs and indexes.
Full-text queries against linked servers.
Full-text queries against arbitrary sets of columns (instead of just one column or all columns).
Specification of the language to be used for word-breaking in an index.
Integrated backup and restore for full-text catalogs.
Full-text indexing for XML data.
Integration with SQL Profiler and logging of index operations.
If you were interested in full-text searching in SQL Server 2000 but ran into brick walls, take another look when the new version comes out. Microsoft's substantial work in this area means that full-text indexing and searching will be better than ever.



---
So it sounds like I may need to upgrade the SQL server to have the best benefits of FULL INDEX SEARCH.