Damn, this is killing me! People rarely generate printed reports in our system, but this issue will make it nearly impossible to do a printed report with more than a few entities, since it takes 7 seconds per entity for the subreport containing the query in question to execute.
On 10/2/07, Rick Root <[EMAIL PROTECTED]> wrote: > 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 > -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:290054 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
