[thelist] SQL Rowcount Problem

[darren]

On Friday, June 21, 2002 at 20:20, Seth Bienek wrote:


SB> I have a query (SQL Server, CF5) that returns up to several thousand
SB> rows as a search result.  Due to the nature of the query, at times it
SB> runs long, and adding to the performance issue (I believe) is the fact
SB> that it returns many columns and many rows of data from the SQL Server
SB> via the network, when I only need to display a few at a time (10-50) to
SB> the user.

how about getting the sproc to only return the results you need?
basically have a temp table with an numeric index and an identifier
for the data you want to retrieve.  when you want to select the main
bulk of your data, join the temp table into the select and limit the
records by the index on the temp table (hope that makes some kind of
sense!)...

you would have something like:

   select
      a.*
   from
      main_tables as a
   inner join tmp_table as b
      on b.id = a.id
   where
      b.index > firstRec   and
      b.index < lastRec

there's a better example on 4guysfromrolla:

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

which is in asp, but mainly deals with the stored proc.  and if you're
interested there's some performance data on ken schaefers adOpenStatic
site:

   http://www.adopenstatic.com/experiments/recordsetpaging.asp

hth,

darren