RE: Performance advantage - LIMIT VS. maxrows?
I believe if you specify maxrows=10 it will pull down the whole resultset and then only grab the first 10 rows. Similar to doing cfoutput query=queryName maxrows=10 Using Top/Limit is preferable. Russ -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2007 1:37 PM To: CF-Talk Subject: Performance advantage - LIMIT VS. maxrows? MX7 / MySQL 5. I need to query a large table of data, but only want to pull the top 10 rows or so. Is there an advantage to using LIMIT 10 in the query, over cfquery maxrows=10? Or vice versa? Thanks, Will ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280680 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Performance advantage - LIMIT VS. maxrows?
I'll have to double check this... but I believe recent versions of CF (as of MX perhaps?) do -not- pull down the whole resultset. If this is the case (and I stress -if-...), then I'd say that using maxrows would be preferrable as it'd make your applications more portable across different databases. On 6/11/07, Russ [EMAIL PROTECTED] wrote: I believe if you specify maxrows=10 it will pull down the whole resultset and then only grab the first 10 rows. Similar to doing cfoutput query=queryName maxrows=10 Using Top/Limit is preferable. Russ -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2007 1:37 PM To: CF-Talk Subject: Performance advantage - LIMIT VS. maxrows? MX7 / MySQL 5. I need to query a large table of data, but only want to pull the top 10 rows or so. Is there an advantage to using LIMIT 10 in the query, over cfquery maxrows=10? Or vice versa? Thanks, Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280681 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Performance advantage - LIMIT VS. maxrows?
That's correct. Unless you NEED the whole dataset, it's always better to limit at the query level, rather than the output level. -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2007 12:49 PM To: CF-Talk Subject: RE: Performance advantage - LIMIT VS. maxrows? I believe if you specify maxrows=10 it will pull down the whole resultset and then only grab the first 10 rows. Similar to doing cfoutput query=queryName maxrows=10 Using Top/Limit is preferable. Russ -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2007 1:37 PM To: CF-Talk Subject: Performance advantage - LIMIT VS. maxrows? MX7 / MySQL 5. I need to query a large table of data, but only want to pull the top 10 rows or so. Is there an advantage to using LIMIT 10 in the query, over cfquery maxrows=10? Or vice versa? Thanks, Will ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280683 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Performance advantage - LIMIT VS. maxrows?
not 100% confirmed, but a 95% confirmation from mr camden: (pasted with permission from #coldfusion on IRC/DALnet) [11:02] [CJ] so in response to a question on cf-talk... i'm wondering if anyone can confirm that recent versions of CF (as of MX?) do NOT pull all records from the database when the MAXROWS attribute is specified in the cfquery tag? [11:03] [CJ] i know older versions pulled all records but just delivered the number specified in the MAXROWS. i remember hearing that this was resolved in newer versions tho [11:03] Boyzoid I don;t recall hearing that [11:03] cfjedimaster correct [11:03] Boyzoid though I rarely use MAXROWS [11:03] cfjedimaster in MX, it uses the JDBC [11:03] cfjedimaster so its cool to use [11:03] cfjedimaster as long as the driver supports it [11:03] [CJ] sweet. thanks :) [11:03] phill_nacelli I think the amount of data returned from the db to cf is still the same isnt' it?? [11:04] cfjedimaster phil, no [11:04] cfjedimaster afaik [11:04] cfjedimaster im not 100% on this [11:04] cfjedimaster but around 95% ;) [11:04] cfjedimaster i dont think it changes the sql- [11:04] cfjedimaster but does a more direct thing to the db [11:04] cfjedimaster ie, Hey db! listen, in this sql, just give me top N rows On 6/11/07, Charlie Griefer [EMAIL PROTECTED] wrote: I'll have to double check this... but I believe recent versions of CF (as of MX perhaps?) do -not- pull down the whole resultset. If this is the case (and I stress -if-...), then I'd say that using maxrows would be preferrable as it'd make your applications more portable across different databases. On 6/11/07, Russ [EMAIL PROTECTED] wrote: I believe if you specify maxrows=10 it will pull down the whole resultset and then only grab the first 10 rows. Similar to doing cfoutput query=queryName maxrows=10 Using Top/Limit is preferable. Russ -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2007 1:37 PM To: CF-Talk Subject: Performance advantage - LIMIT VS. maxrows? MX7 / MySQL 5. I need to query a large table of data, but only want to pull the top 10 rows or so. Is there an advantage to using LIMIT 10 in the query, over cfquery maxrows=10? Or vice versa? Thanks, Will ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280688 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Performance advantage - LIMIT VS. maxrows?
Charlie Griefer wrote: I'll have to double check this... but I believe recent versions of CF (as of MX perhaps?) do -not- pull down the whole resultset. If this is the case (and I stress -if-...), then I'd say that using maxrows would be preferrable as it'd make your applications more portable across different databases. Even then limit is still preferable performance wise. It gives the database the opportunity to better optimize the query and choose an execution plan that may have a higher cost at a large number of records, but a lower cost at a low number of records. Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280691 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
