Efficient record paging in CFMX MSSQL

[bmyers]

When we moved from CF5 to CFMX, we saw the read speeds on our MS SQL2000
database increase by 1500%.

A simple read coded as shown below, ran in less than 1 second in CF 5, but
takes 30+seconds in CF6.

<CFQUERY name="searchcontent" datasource="#Database#" username="#USER#"
password="#PASS#">
SELECT page_number, headline, byline,
department,html,alternate_link,show_rss,active_dat e,image1,access,align1,active_
date
FROM content
ORDER BY active_date DESC, content_rank ASC, Page_number DESC
</CFQUERY>

In CF 5, the above read would require less than 1 second. But in CFMX it
takes 30 seconds or more.

We've reduced some of the problem by including a 'top 100' in the read
statement where possible.

But when we need to page through the database, it can take 30 seconds just to
read the database and then display a few records at a time.

Because of this, we are looking for an efficient paging system (back / next).

All of the ones we have found so far work after the query, which means the
entire database is read each time a new page is requested.

Even though we only show 10 records per page, CF still reads the entire
database within a paging system.

Again, this wasn't a problem with CF5. But with CF MX and MS SQL2000, it is
a huge problem for us. 30+ seconds to read 50,000 records.

Any suggestions? (We are hosted by Intermedia.net, CF 6.1, MS SQL 2000).

Bill

[Sojovi]

[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?event=view&id=KC.tn_19[/url]
570&extid=tn_19570&dialogID=25898513&iterationID=2 &sessionID=96303695ccb6315a1f8
0&stateID=0+0+25902532&mode=simple

Check that link, maybe could help.

Regards