September 20, 2010 at 10:35 pm
Hi Anyone can help me below
I want to create index for below scripts in sql 2005. the correct index. Im having a performance issue for this.
selecta16.Country_Code Country_Code,
a12.cmetric_id cmetric_id,
sum(ISNULL((a11.Sales_Amount * a12.cmetric_p1), 0)) WJXBFS1,
sum(ISNULL((a11.Refund_Count * a12.cmetric_p2), 0)) WJXBFS2,
ISNULL((sum(ISNULL((a11.Sales_Amount * a12.cmetric_p3), 0)) / NULLIF(sum(ISNULL((a11.Refund_Count * a12.cmetric_p3), 0)), 0)), 0) WJXBFS3
into #ZZT010E5QHNMD009
fromSUM_TFS_Form_Insightsa11
cross joinLU_MSTR_CMETRICSa12
joinLU_Montha13
on (a11.YM_Key = a13.LY_YM_Key)
join#ZZT010E5QHNMQ005pa14
on (a11.Ctry_Merc_Key = pa14.Com_CtryMercKey)
join#ZZT010E5QHNMQ000pa15
on (a11.Tourist_Country_Code = pa15.Tourist_Country_Code)
joinLU_TFS_Index_Country_Competitors_Countrya16
on (a11.Country_Code = a16.Competitors_Country_Code)
wherea13.YM_Key between (SELECT (YR_Key*100)+1 FROM LU_TFS_Data_Date) and (SELECT YM_Key FROM LU_TFS_Data_Date)
group bya16.Country_Code,
a12.cmetric_id
thanks
Ariel
September 20, 2010 at 10:39 pm
Ayie,
I'll save you some trouble before the optimization experts swing through and they can look at your code immediately, you'll want to follow the directions in this link:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
What you've got above is barely the first part in figuring out what index(es) you're going to need.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 2:58 am
You're cross joining two tables? Why?
That in itself will give performance problems if those tables are largish.
And yes, table definitions, index definitions and execution plan please, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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