[thelist] SQL Rowcount Problem

[Erik Mattheis]

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

__________________________________________