I figured this out finally. Turned out that my data types for the "row_id" field were different... my query_results.row_id was an int, while giftab1.row_id was a nvarchar(7)... I didn't make the original giftab1 table so I have no idea WHY it was such, since row_id contains only unique integers!)
Once I matched up the data types, the clustered index scan became a clustered idnex seek and performed MUCH better. Rick On 10/3/07, Rick Root <[EMAIL PROTECTED]> wrote: > 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 > -- Rick Root Check out CFMBB, BlogCFM, ImageCFC, ImapCFC, CFFM, and more at www.opensourcecf.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:290158 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
