Create Index SQL 2005

Question

Create Index SQL 2005

Ayie

SSC Eights!

Points: 828
More actions
September 20, 2010 at 10:35 pm

#228140

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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://qa.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.

- Craig Farrell

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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://qa.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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass