LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » Perfomance on the Following Query

Perfomance on the Following Query

Options

Previous Topic · Next Topic

vuyiswamb

Posted: Thursday, June 04, 2009 7:43:48 AM

Rank: Newbie

Joined: 6/4/2009
Posts: 1
Points: 3
Where do you live?: South Africa

Good Afternoon All

I have Table Defined as

Code:

/****** 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

Code:

/****** 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

Code:

--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

Code:

How can i improve the Perfomance of this Insert statements

Thank you

Scott Whigham

Posted: Friday, June 05, 2009 9:53:38 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

First thing I notice is that there is no primary key. Is this an oversight? It seems as though you've created a unique clustered index when you should have created a primary key (which would have actually created the unique clustered index for you).

Second thing is to add indexes - add a nonclustered index on "domn, slot"

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.