November 24, 2008 at 9:57 pm
Hi,
I am currently working with this one huge table with over 250 million rows. I am trying to create an aggregate table on the top of this table to support my reports. The issue is that my aggregation query is taking somewhere around 1 hour to execute. Please suggest how I can improve the execution. I am attaching the estimated query execution plan for review.
PS: I renamed the execution plan to .jpg. Please rename it as .sqlplan.
November 24, 2008 at 11:11 pm
In addition to your execute plan, we will also need to see your query and the DDL for the table including all indexes that are defined on it as well.
November 25, 2008 at 11:43 am
The first problem is that you have two table scans. Second problem is that you're getting a hash join because you have no indexes.
Add indexes to support the join and/or the aggregation. As a first suggestion
LU_DATE (DateID, BCWeekID)
FA_DAILY_IMP (DateID) INCLUDE (MarketID, MediaSubTypeID, DaypartCode, BaseSegmentID, CompanyID, AdvertiserID, DistributorID, ProgramID, CreativeID, ClassificationID, AttributeID, Impressions)
If you're willing to create a very wide index specifically for this query, move all but the last column of the second index into the key column list.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply