Philip,
You wrote:
When data is being transferred from COM or SQL to CF it will send over the
amount of records requested, in this case it's done via
"MyConnection.Execute(SQL, 0, 8)" which whill only send over upto 9 records
However, the query used below will return just however many records are in
the recordset - 100s. The (0, 8) parameters don't appear to apply to the
MaxRecords property.
I recognise the problems of GETTING ALL the records and then USING SOME of
them and had already tried the 'TOP n FROM' in the SQL statement but Index
Server barfed it back - invalid syntax as it is expecting to find a column
named 'TOP'
Here's the SQL statement:
/* set the 'newscategory' META information */
SQL = "SET PROPERTYNAME 'd1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1' PROPID
'newscategory' AS theCategory ";
/* look only in the news directory, don't include subfolders */
SQL = SQL & "SELECT Characterization, DocTitle, VPath FROM SCOPE (' SHALLOW
TRAVERSAL OF ""/News"" ') ";
SQL = SQL & "WHERE CONTAINS('#frmSearchText#') > 0 ";
SQL = SQL & "AND theCategory = 'General' ";
SQL = SQL & "ORDER BY #frmSortCol# #frmSortOrder#";
Since this is a site and the site doesn't have that many indexed pages it is
probable that there will not be more than 1000 or so records returned. But
I thought there might be a way to get the 'TOP n' rather than get the whole
kaboodle and then use just a portion of it. Like you say, doing it that way
isn't particulary efficient. And this code might get used elsewhere where
there may be 1000s of records returned.
Thanks,
Keith
-----Original Message-----
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: 26 January 2001 14:17
To: CF-Talk
Subject: RE: Index Server and setting maxRecords
OK, here's why/why not to use the MaxRows in CFLOOP and CFOUTPUT;
When data is being transferred from COM or SQL to CF it will send over the
amount of records requested, in this case it's done via
"MyConnection.Execute(SQL, 0, 8)" which whill only send over upto 9 records
If you let it send over all records, they have to be transferred into CF
before it can decide how many rows to display
If you have hundreds of thousands of records being sent to CF, this can be
VERY slow, and then letting CF just show the top 9 is just daft
A similar example is doing it via SQL
<CFQUERY NAME="myQuery" DATASOURCE="myDSN" MAXROWS=10>
SELECT ID FROM myTable
</CFQUERY>
may return thousands of records into CF before CF trims out the top 10
<CFQUERY NAME="myQuery" DATASOURCE="myDSN">
SELECT TOP 10 ID FROM myTABLE
</CFQUERY>
will only return the top 10 records to CF, which will be MUCH faster for SQL
and CF
MAXROWS should only be used if you need both the complete list and the top
part of the list
Hope this explains :-/
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
