Hi Folks, I have two questions: 1. Does anyone have any test results that actually show that using cfqueryparam speeds up oracle queries? All my testing shows that it actually slows down the queries. 2. I have the following two queries that I'll eventually want to use to construct a graph comparing "your farm" to the avg of "all farms." Maybe it's just a Friday, but it seems like there should be some way for me to do this in one query, instead of two. But, I can't figure out what it would be. (I've already played with Decode and union.) If I can get it into one query, I think I can write a stored procedure that will (hopefully) be faster. But, I don't know how to write a stored procedure that includes two resultsets, off hand. (Hm..unless you just include two "out" parameters..... I'll have to try that.) Any ideas to speed up these queries would be great, too. There's over 100,000 records in this table, and it's moving kinda sluggish. -Deanna <cfquery name="getsizeyours" password="#pword#" username="#uname#" datasource="#dsn#"> SELECT cowcount AS totalcowsyours, trunc(fulldate, 'Mon') AS month FROM coopep.dg_herddata WHERE fulldate between <cfqueryparam value="#dateformat(yearpast, "dd-mmm-yyyy")#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#dateformat(today, "dd-mmm-yyyy")#" cfsqltype="CF_SQL_DATE"> AND herdcode = <cfqueryparam value="#herdcode#" cfsqltype="CF_SQL_INTEGER"> </cfquery> <cfquery name="getsizeall" password="#pword#" username="#uname#" datasource="#dsn#"> SELECT AVG(cowcount) as totalcowsall, trunc(fulldate, 'Mon') AS month FROM coopep.dg_herddata WHERE fulldate between <cfqueryparam value="#dateformat(yearpast, "dd-mmm-yyyy")#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#dateformat(today, "dd-mmm-yyyy")#" cfsqltype="CF_SQL_DATE"> AND trunc(fulldate, 'Mon') IN (SELECT trunc(fulldate, 'Mon') from coopep.dg_herddata WHERE herdcode = <cfqueryparam value="#herdcode#" cfsqltype="CF_SQL_INTEGER">) GROUP BY trunc(fulldate, 'Mon') ORDER BY month </cfquery> Deanna Schneider Interactive Media Developer [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
