[thelist] SQL Rowcount Problem
darren
darren at web-bitch.co.uk
Sat Jun 22 10:31:00 CDT 2002
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
More information about the thelist
mailing list