[thelist] SQL Rowcount Problem

Chris Blessing webguy at mail.rit.edu
Fri Jun 21 14:36:01 CDT 2002


Seth-

I've been doing the same thing (today, actually, heh) and I've combined TOP
with ADO recordset caching.  Since you're using CF this will probably differ
but I'm sure there's a way to use it.

First off, I only select the top 10 x page number of records per page of
output.  So in my case, it's a search engine and it returns, say, "results
1-10 of 2355".  That's using a SELECT TOP 10... query.  If the user says "ok
I want to skip to page 3 of the search results" I'll do a SELECT TOP 30...
query.

That's really the smaller part of the equation.  It only reduces DB overhead
on the most popular (i.e. pages 1-2-3) pages of the search results.  If you
dare to go any further into your results, not only will you find pages not
likely related to your query, but you'll also have to wait to get that
misinformation.  :D

Anyhow, the recordset paging thing is pretty cool.  If you can use it, do by
all means, but again I'm not sure how this would work with CF.  Take a look
at the ASP equiv. here:

http://www.4guysfromrolla.com/webtech/121298-1.shtml

HTH!

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> Afternoon, y'all.  I have this problem, see...
>
> I have a query (SQL Server, CF5) that returns up to several thousand
> rows as a search result.  Due to the nature of the query, at times it
> runs long, and adding to the performance issue (I believe) is the fact
> that it returns many columns and many rows of data from the SQL Server
> via the network, when I only need to display a few at a time (10-50) to
> the user.
>
> I need to pull all the rows in order to return the number of matches,
> right?  Is there a way to avoid having to handle all the data (and it's
> overhead) when I only really want the first x rows and a count of the
> total number of matches?
>
> I can use the TOP operator, but it of course returns as the recordcount
> only the number of rows returned..  I know there's a way around this,
> but the documentation doesn't seem to be much help to me thus far..
>
> Any help is greatly appreciated!
>
> Thanks,
>
> Seth




More information about the thelist mailing list