[thelist] SQL Rowcount Problem
Erik Mattheis
gozz at gozz.com
Fri Jun 21 18:58:00 CDT 2002
There might be a better way of doing it, but something like this
might work: have an ORDER BY clause and pass to the next set of 10
the value in the last row of whatever you're ordering by ...
something like:
<cfparam name="url.last_id" default="0">
<cfquery datasource="dsn" name="results">
SELECT TOP 10 my_table_id
FROM my_table
WHERE my_table_id > #Val(url.last_id)#
ORDER BY my_table_id
</cfquery>
<a href="result.cfm?last_id=#results['my_table_id'][10]#">
that would make the next SQL statement:
SELECT TOP 10 my_table_id
FROM my_table
WHERE my_table_id > 10
ORDER BY my_table_id
and the one after that:
SELECT TOP 10 my_table_id
FROM my_table
WHERE my_table_id > 20
ORDER BY my_table_id
etc.
>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?
--
__________________________________________
- Erik Mattheis
(612) 377 2272
http://goZz.com/
Through Mid July
9am - 4pm M-F:
(952) 838 7698
__________________________________________
More information about the thelist
mailing list