Perfomance on the Following Query

[vuyiswam]

Good Morning All

I have Table Defined as

/****** Object: Table [dbo].[EXP_REL_SLOT_DOMN] Script Date: 06/04/2009 08:38:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EXP_REL_SLOT_DOMN](
[SLOT] [int] NOT NULL,
[DOMN] [int] NOT NULL,
[PREF] [int] NOT NULL
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'references TBL_SLOT_ALLC.ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EXP_REL_SLOT_DOMN', @level2type=N'COLUMN', @level2name=N'SLOT'

indexed as

/****** Object: Index [EXP_REL_SLOT_DOMN_INDEX] Script Date: 06/04/2009 08:41:01 ******/
CREATE UNIQUE CLUSTERED INDEX [EXP_REL_SLOT_DOMN_INDEX] ON [dbo].[EXP_REL_SLOT_DOMN]
(
[SLOT] ASC,
[DOMN] ASC,
[PREF] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [PRIMARY]

And i have Two insert statements that takes 29 seconds each

--29 seconds
INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn
and sd1.slot > sd2.slot

--29 seconds
INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2
on sd1.domn = sd2.domn
and sd1.slot < sd2.slot

How can i improve the Perfomance of this Insert statements

Thank you

[olivthill2]

If I'm not wrong, you only have one index, and it is based on three fields. Unfortunately your queries don't use these three fields, and therefore no index is used. The tables are read from the start to the end. In order to have a better speed, I would suggest to add a secondary index on the DOMN column.