Optimization in SQL

[vuyiswam]

Good Day all

I need you Help to Optmize this Query. Am doing a multi dimension Query

DELETE #ActvClashes
FROM MTM_ACTV_SETS AS1 INNER JOIN MTM_ACTV_SETS AS2
ON (left(AS2.SETS,len(as2.sets)-2)=left(AS1.SETS,len(as1.sets)-2))AND(as1.sets <> as2.sets)
INNER JOIN #ActvClashes CA ON CA.ACTV1=AS1.ACTV AND CA.ACTV2=AS2.ACTV
WHERE AS1.ACTV <> AS2.ACTV
AND (AS1.SETS like '%_R' or AS1.SETS like '%_L')

Thanks in advance

[Alsvha]

Impossible to say without knowing the database design, index structure and so on.......

You should take a look at the execution plan and go from there. I suspect you have some poor index utilization in your ON and WHERE clause.

[vuyiswam]

Good morning Alsvha

i have an index defined like this


USE [AT_26MAY_09_1240]
GO
/****** Object: Index [NEW] Script Date: 05/27/2009 08:34:25 ******/
CREATE UNIQUE CLUSTERED INDEX [NEW] ON [dbo].[MTM_ACTV_SETS]
(
[ID] ASC,
[Actv] ASC,
[Sets] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]



and on my sql statement, am using a a temp table, so i createad an index for the temp like this


CREATE UNIQUE INDEX IX_1 on #ActvClashes (ACTV1, ACTV2, CLASHES)
update statistics #ActvClashes(IX_1)


Defining indexes incorrectly can be a perfomance hit ?

Thank you