Re: Query speed diference
If your DB is Oracle, you can easily use MERGE SQL command for this purpose. HTH Qasim On 8/30/07, Nick de Voil [EMAIL PROTECTED] wrote: Jay Which of these two methods would be faster? - Method 1 - cfquery SELECT * FROM table WHERE someID = #someVar# /cfquery cfif query.recordCount IS 1 cfqueryUPDATE table.../cfquery cfelse cfqueryINSERT INTO table.../cfquery /cfif - Method 2 - cfquery DELETE FROM table WHERE someID = #someVar# /cfquery cfqueryINSERT INTO table.../cfquery You can achieve an improvement by either: - changing your logic so that you know whether to do an update or insert without needing to check the record ID against the database; or - whichever of the 2 methods you use, combining the 2 SQL statements into one stored procedure or at least one CFQUERY. Nick ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287545 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query speed diference
Which of these two methods would be faster? - Method 1 - cfquery SELECT * FROM table WHERE someID = #someVar# /cfquery cfif query.recordCount IS 1 cfqueryUPDATE table.../cfquery cfelse cfqueryINSERT INTO table.../cfquery /cfif - Method 2 - cfquery DELETE FROM table WHERE someID = #someVar# /cfquery cfqueryINSERT INTO table.../cfquery I know the difference will be small but even if it is only 10ms per iteration that is a 5 minute difference over 30,000 records and that adds up to a 90 minute difference over the course of a day to the process we are running. -- Jay No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.12/979 - Release Date: 29/08/2007 20:21 ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287429 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query speed diference
Jay Which of these two methods would be faster? - Method 1 - cfquery SELECT * FROM table WHERE someID = #someVar# /cfquery cfif query.recordCount IS 1 cfqueryUPDATE table.../cfquery cfelse cfqueryINSERT INTO table.../cfquery /cfif - Method 2 - cfquery DELETE FROM table WHERE someID = #someVar# /cfquery cfqueryINSERT INTO table.../cfquery You can achieve an improvement by either: - changing your logic so that you know whether to do an update or insert without needing to check the record ID against the database; or - whichever of the 2 methods you use, combining the 2 SQL statements into one stored procedure or at least one CFQUERY. Nick ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287430 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
