What's the difference between QUERY_RESULTS and GIFTREPORTS? Do things change if you select over query_results and join giftab1?
-- Andrew -----Original Message----- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 02, 2007 3:32 PM To: CF-Talk Subject: OT: Query Help Man, I'm having all kinds of brain farts today. I'm trying to optimize the following query: SELECT DISTINCT (fieldlist) FROM GIFTAB1 B INNER JOIN QUERY_RESULTS C ON B.ROW_ID = C.ROWID WHERE C.QUERYID = #param.QUERYID# AND C.ENTITYID = '#param.ENTITY_ID#' ORDER BY B.ENTITY_ID, B.CREDTDAT DESC GIFTAB1 contains almost million rows of data, and query_results contains a varying amount of rows, depending on activity, time of day, etc.. specifically in this case, QUERY_RESULTS contains 341 rows that match the aboe criteria, and currently contains about 8000 rows total. This query takes 7 seconds to return 7 rows of data given a specific queryid and entity id. I have indexes on all relevant columns, and row_id on giftab1 is actually a unique clustered index. I show the query execution plan, and my biggest hit (92%) is a "Clustered Index Scan" on giftab1 ... the row id join. A similar query in our legacy application performs MUCH better: SELECT DISTINCT (fieldlist) FROM GIFTAB1 B INNER JOIN GIFTREPORTS C ON B.ROW_ID = C.ROW_ID WHERE C.USERNAME='GROLWR' AND C.ENTITY_ID = '0000000088' ORDER BY B.ENTITY_ID, B.CREDTDAT DESC The problem appears to be in my first query, that the clustered indes scan lists "actual number of rows" as 3.9 million - the FASTER query doesn't do this... the faster query uses an index scan on giftreports and an index seek on giftab1. Both contain 7 rows. -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289992 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
