RE: DB Pagination Question
Andrew, To add to what Adam said, you can even do: select *, (select count(*) from table) as totalRecords from table To grab the total number of records as a column. NOTE: You want to try to avoid using * in your select statements--I'm using it for brevity. -Dan -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 28, 2006 11:46 PM To: CF-Talk Subject: Re: DB Pagination Question Andrew, Don't use RecordCount on a query that returns the actual rows. Instead, perform a query that just returns the Count(*) of the rows that would satisfy your WHERE clause. It's much more performant and requires a lot less of your server resources. Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Andrew Grosset To: CF-Talk Sent: Tuesday, March 28, 2006 8:53 PM Subject: Re: DB Pagination Question Dan, thanks for posting. Presumably you need to do another query initially to determine how many pages potentially to display dependent on the recordcount? (ie if there were 1000 records (recordcount?) and you elected to display 10 records/page that would be 100 pages). ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236390 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: DB Pagination Question
Thank you Adam Dan, I am using sql 2000 and I couldn't get your example to run but modifying it using the IN keyword it does! is this ok ? (it works) cfquery name=a datasource=** select top 10 id, (select count(id) from myTable) as totalRecords from myTable where id IN (select top 20 id from myTable order by id asc) order by id desc /cfquery Andrew. Andrew, To add to what Adam said, you can even do: select *, (select count(*) from table) as totalRecords from table To grab the total number of records as a column. NOTE: You want to try to avoid using * in your select statements--I'm using it for brevity. -Dan ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236517 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: DB Pagination Question
Dan, thanks for posting. Presumably you need to do another query initially to determine how many pages potentially to display dependent on the recordcount? (ie if there were 1000 records (recordcount?) and you elected to display 10 records/page that would be 100 pages). Andrew. You can do it with w/pure SQL server, but it can be pretty expensive depending on the query. The idea is to do: select top 10 * from ( select top 40 * from table order by tableId asc ) order by tableId desc You have to match up the where clauses exactly. The idea is the virtual table grabs the current page number and add one then multiply by the number of records to display (records*(page+1) = 10*(3+1).) This would pull out the top 40 records. You then order by descending grabbing out just the number of items on the page. This will grab out records 31-40. Hope this helps! -Dan ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236376 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: DB Pagination Question
Andrew, Don't use RecordCount on a query that returns the actual rows. Instead, perform a query that just returns the Count(*) of the rows that would satisfy your WHERE clause. It's much more performant and requires a lot less of your server resources. Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Andrew Grosset To: CF-Talk Sent: Tuesday, March 28, 2006 8:53 PM Subject: Re: DB Pagination Question Dan, thanks for posting. Presumably you need to do another query initially to determine how many pages potentially to display dependent on the recordcount? (ie if there were 1000 records (recordcount?) and you elected to display 10 records/page that would be 100 pages). Andrew. You can do it with w/pure SQL server, but it can be pretty expensive depending on the query. The idea is to do: select top 10 * from ( select top 40 * from table order by tableId asc ) order by tableId desc You have to match up the where clauses exactly. The idea is the virtual table grabs the current page number and add one then multiply by the number of records to display (records*(page+1) = 10*(3+1).) This would pull out the top 40 records. You then order by descending grabbing out just the number of items on the page. This will grab out records 31-40. Hope this helps! -Dan ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236380 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: DB Pagination Question
Are you using SQL Server 2005? Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 10:45 AM Subject: DB Pagination Question Hi All, I am wondering what the best way to handle the following situation is. I have a database with approx 100,000 records. Users can search on these records with multiple criteria. Some result sets can contain 10,000 records or more. Also, there is a possibility of sorting the results. Using CF SQL server, what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? TIA. - jim ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236246 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: DB Pagination Question
No, MSSQL 2000 -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 1:14 PM To: CF-Talk Subject: Re: DB Pagination Question Are you using SQL Server 2005? Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 10:45 AM Subject: DB Pagination Question Hi All, I am wondering what the best way to handle the following situation is. I have a database with approx 100,000 records. Users can search on these records with multiple criteria. Some result sets can contain 10,000 records or more. Also, there is a possibility of sorting the results. Using CF SQL server, what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? TIA. - jim ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236249 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: DB Pagination Question
Jim Curran wrote: what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? Unfortunately, I believe those are your only two options. You either cache the query into resident memory (memory hog) or you run the query at each request. You can do little things like adding the MaxRows attribute to your query (which will lessen the amount of memory used per query), or using views instead of joins (to improve query speed), but I know of no other way to accomplish what you're trying to do then with query caching or a query on every page. HTH! -- Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236251 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: DB Pagination Question
I believe this was talked about a couple of months back and some people suggested using a method of selecting not only max rows but to find do a hack to get the start row by using the IN keyword to somehow filter out the ones for the pages you're already past. That would do it on the DB side instead of passing the whole dataset to CF and then using the startrow and maxrows attributes of CF. May want to search the archives. John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -Original Message- From: Jordan Michaels [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 1:49 PM To: CF-Talk Subject: Re: DB Pagination Question Jim Curran wrote: what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? Unfortunately, I believe those are your only two options. You either cache the query into resident memory (memory hog) or you run the query at each request. You can do little things like adding the MaxRows attribute to your query (which will lessen the amount of memory used per query), or using views instead of joins (to improve query speed), but I know of no other way to accomplish what you're trying to do then with query caching or a query on every page. HTH! -- Warm regards, Jordan Michaels Vivio Technologies http://www.viviotech.net/ Blue Dragon Alliance Member [EMAIL PROTECTED] ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236255 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: DB Pagination Question
That's too bad. With SQL Server 2005 you can do this: With Books AS (select isbn, title, ROW_NUMBER() OVER(order by Title) as RowNumber from book) select * from Books where RowNumber between 2 and 14 and then just use a simple calculation for the starting and ending row numbers of the page you want to retrieve. This is how we perform efficient custom pagination for ASP.NET 2.0 GridView controls that use object data sources. The equivalent SQL Server 2000 techniques I've seen all populate temporary tables with the *entire* contents of the target table, then select only those needed from the temp table. Try *that* under load! Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 1:27 PM Subject: RE: DB Pagination Question No, MSSQL 2000 -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 1:14 PM To: CF-Talk Subject: Re: DB Pagination Question Are you using SQL Server 2005? Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 10:45 AM Subject: DB Pagination Question Hi All, I am wondering what the best way to handle the following situation is. I have a database with approx 100,000 records. Users can search on these records with multiple criteria. Some result sets can contain 10,000 records or more. Also, there is a possibility of sorting the results. Using CF SQL server, what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? TIA. - jim ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236265 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: DB Pagination Question
You can do it with w/pure SQL server, but it can be pretty expensive depending on the query. The idea is to do: select top 10 * from ( select top 40 * from table order by tableId asc ) order by tableId desc You have to match up the where clauses exactly. The idea is the virtual table grabs the current page number and add one then multiply by the number of records to display (records*(page+1) = 10*(3+1).) This would pull out the top 40 records. You then order by descending grabbing out just the number of items on the page. This will grab out records 31-40. Hope this helps! -Dan -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 2:52 PM To: CF-Talk Subject: Re: DB Pagination Question That's too bad. With SQL Server 2005 you can do this: With Books AS (select isbn, title, ROW_NUMBER() OVER(order by Title) as RowNumber from book) select * from Books where RowNumber between 2 and 14 and then just use a simple calculation for the starting and ending row numbers of the page you want to retrieve. This is how we perform efficient custom pagination for ASP.NET 2.0 GridView controls that use object data sources. The equivalent SQL Server 2000 techniques I've seen all populate temporary tables with the *entire* contents of the target table, then select only those needed from the temp table. Try *that* under load! Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 1:27 PM Subject: RE: DB Pagination Question No, MSSQL 2000 -Original Message- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 1:14 PM To: CF-Talk Subject: Re: DB Pagination Question Are you using SQL Server 2005? Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com - Original Message - From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 10:45 AM Subject: DB Pagination Question Hi All, I am wondering what the best way to handle the following situation is. I have a database with approx 100,000 records. Users can search on these records with multiple criteria. Some result sets can contain 10,000 records or more. Also, there is a possibility of sorting the results. Using CF SQL server, what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? TIA. - jim ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236271 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
