Large Recordset Display Problem
I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Large Recordset Display Problem
Try caching the query. That will keep it from hitting the database again. Clint - Original Message - From: admin [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 12:35 PM Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
You could also put the entire result set into a group of div blocks and let the user page through them using visibility parameters - then you wouldn't have to re-load the page. - Jim -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 12:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
You'd probably be better off storing the recordset in the current user's session, since they will undoubtedly user-specific. Then you can just do QofQs to get the partial display sets. Caching the query in the server's query cache could interfere with other cached queries, and might require a single user to hit the DB more than once if his recordset is bumped from the pool before he's done looking through it. Of course, if you manually cache in the session, you'll have to make sure that you clear that data when you're done, or you'll just eat up all your memory. Another option, if the total result (all possibel records) isn't overly large, and doesn't change frequently, woudl be to cache the whole thing in memory, and then run QofQs on that recordset for user searches. That'll skip the database interaction as well. barneyb -Original Message- From: Clint [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 10:45 AM To: CF-Talk Subject: Re: Large Recordset Display Problem Try caching the query. That will keep it from hitting the database again. Clint - Original Message - From: admin [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 12:35 PM Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Large Recordset Display Problem
Thanks all of you for the great info - now the for the stupid question ! do I use cachedwithin to get the data stored as a session variable ? I guess the question really is how do I stick a query in to session vars tia Richard - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:11 AM Subject: RE: Large Recordset Display Problem Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
cfquery ... name=searchresult ... /cfquery cfset session.searchresult = searchresult / the cachedwithin attribute will cache the recordset in the server's query cache, as will the cachedafter attribute. barneyb -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:40 AM To: CF-Talk Subject: Re: Large Recordset Display Problem Thanks all of you for the great info - now the for the stupid question ! do I use cachedwithin to get the data stored as a session variable ? I guess the question really is how do I stick a query in to session vars tia Richard - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:11 AM Subject: RE: Large Recordset Display Problem Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
Couldn't be simpler... CFQUERY NAME=SESSION.myQuery -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:40 PM To: CF-Talk Subject: Re: Large Recordset Display Problem Thanks all of you for the great info - now the for the stupid question ! do I use cachedwithin to get the data stored as a session variable ? I guess the question really is how do I stick a query in to session vars tia Richard - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:11 AM Subject: RE: Large Recordset Display Problem Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
My apologies... for locking reason's Barney's method is better: cfquery ... name=searchresult ... /cfquery cfset session.searchresult = searchresult / Make sure, if you're not using CFMX, that the CFSET statement is locked using CFLOCK. -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: Mosh Teitelbaum [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:48 PM To: CF-Talk Subject: RE: Large Recordset Display Problem Couldn't be simpler... CFQUERY NAME=SESSION.myQuery -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:40 PM To: CF-Talk Subject: Re: Large Recordset Display Problem Thanks all of you for the great info - now the for the stupid question ! do I use cachedwithin to get the data stored as a session variable ? I guess the question really is how do I stick a query in to session vars tia Richard - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:11 AM Subject: RE: Large Recordset Display Problem Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Large Recordset Display Problem
Thank you all very much for this great info. Now maybe I can get on with thos project and stop worrying about th eproblems in the world. - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:54 AM Subject: RE: Large Recordset Display Problem My apologies... for locking reason's Barney's method is better: cfquery ... name=searchresult ... /cfquery cfset session.searchresult = searchresult / Make sure, if you're not using CFMX, that the CFSET statement is locked using CFLOCK. -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: Mosh Teitelbaum [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:48 PM To: CF-Talk Subject: RE: Large Recordset Display Problem Couldn't be simpler... CFQUERY NAME=SESSION.myQuery -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:40 PM To: CF-Talk Subject: Re: Large Recordset Display Problem Thanks all of you for the great info - now the for the stupid question ! do I use cachedwithin to get the data stored as a session variable ? I guess the question really is how do I stick a query in to session vars tia Richard - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:11 AM Subject: RE: Large Recordset Display Problem Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Large Recordset Display Problem
Actually, that's not the reason I did it that way (although you're logic probably more relevant). I do it that way because you have to declare functions with this two-step process. I've adopted it across my code for consistency's sake. For example, this won't work: cffunction name=request.myFunc /cffunction you have to do this: cffunction name=temp /cffunction cfset request.myFunc = temp / barneyb -Original Message- From: Mosh Teitelbaum [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:54 AM To: CF-Talk Subject: RE: Large Recordset Display Problem My apologies... for locking reason's Barney's method is better: cfquery ... name=searchresult ... /cfquery cfset session.searchresult = searchresult / Make sure, if you're not using CFMX, that the CFSET statement is locked using CFLOCK. -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: Mosh Teitelbaum [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:48 PM To: CF-Talk Subject: RE: Large Recordset Display Problem Couldn't be simpler... CFQUERY NAME=SESSION.myQuery -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 2:40 PM To: CF-Talk Subject: Re: Large Recordset Display Problem Thanks all of you for the great info - now the for the stupid question ! do I use cachedwithin to get the data stored as a session variable ? I guess the question really is how do I stick a query in to session vars tia Richard - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 11:11 AM Subject: RE: Large Recordset Display Problem Richard: There are two steps involved in doing this, data retrieval and data display. On the retrieval side, as others have mentioned, you'd be well served caching the results of the query. If the query is user-specific, cache it in SESSION. If the query is not user-specific, you might try adding it to ColdFusion's query cache. If the query is not user-centric and will not change over time, cache it in APPLICATION. On the display side, regardless of how it's cached, you can use the STARTROW and MAXROWS attributes of the CFOUTPUT tag to make a prev/next n style display. Your code would look something like: !--- Init MAXROWS and STARTROW --- CFPARAM NAME=URL.StartRow DEFAULT=1 CFPARAM NAME=URL.MaxRows DEFAULT=10 !--- Output up to MaxRows results starting from StartRow --- CFOUTPUT QUERY=myQuery MAXROWS=#URL.MaxRows# STARTROW=#URL.StartRow# #myQuery.Col1# - #myQuery.Col2#BR /CFOUTPUT !--- Display prev/next MaxRows links --- CFIF myQuery.RecordCount NEQ 0 CFIF URL.StartRow GT URL.MaxRows CFSET tmpStartRow = URL.StartRow - URL.MaxRows A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow###60; Previous #URL.MaxRows# Results/A | /CFIF CFSET EndRow = URL.StartRow + URL.MaxRows - 1 Results #URL.StartRow# - CFIF EndRow LT myQuery.RecordCount #EndRow# CFELSE #myQuery.RecordCount# /CFIF CFIF EndRow LT myQuery.RecordCount CFSET tmpStartRow = URL.StartRow + URL.MaxRows CFIF EndRow + URL.MaxRows LTE myQuery.RecordCount CFSET nextNum = URL.MaxRows CFELSE CFSET nextNum = myQuery.RecordCount - EndRow /CFIF | A HREF=results.cfm?MaxRows=#URL.MaxRows#StartRow=#tmpStartRow#Next #nextNum# Results ##62;/A /CFIF /CFIF HTH -- Mosh Teitelbaum evoch, LLC Tel: (301) 942-5378 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: admin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 1:36 PM To: CF-Talk Subject: Large Recordset Display Problem I have a query that returns the results of a search. Potentially this could be a few hundred records. What I want to be able to do is execute the query once (as it's a slow query) but then only display a limited number of records per page (25 or so) and have some navigation to move thru the results, but with out re-doing the search. Any thoughts ? TIA Richard ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http
