Perfomance on the Following Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Perfomance on the Following Query

Author

Topic

vuyiswamb
Starting Member

6 Posts

Posted - 2009-06-04 : 08:12:42

Good Day 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'[/CODE]

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][/CODE]

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]

[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

Do not be tolerent to ignorance but understanding illetaracy

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-04 : 08:17:31

Instead of two insert statements try the below

INSERT into tempslot
select distinct sd1.slot as s1, sd2.slot as s2
from [dbo].[EXP_REL_SLOT_DOMN] sd1 WITH (NOLOCK)
inner join [dbo].[EXP_REL_SLOT_DOMN] sd2 WITH (NOLOCK)
on sd1.slot <> sd2.slot and sd1.domn = sd2.domn

vuyiswamb
Starting Member

6 Posts

Posted - 2009-06-04 : 08:19:53

This Gets more Slower is you combine the queries.

Do not be tolerent to ignorance but understanding illetaracy

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-04 : 08:25:54

Have U tried the above what i suggested earlier???

else try the below

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.slot > sd2.slot and sd1.domn = sd2.domn

--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.slot < sd2.slot and sd1.domn = sd2.domn

Order in join condition is kept as column order while defining Index..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 08:34:45

[code]INSERT INTO TempSlot
(
s1,
s2
)
SELECT sd1.slot AS s1,
sd2.slot AS s2
FROM (
SELECT domn,
slot
FROM dbo.EXP_REL_SLOT_DOMN
GROUP BY domn,
slot
) AS sd1
INNER JOIN (
SELECT domn,
slot
FROM dbo.EXP_REL_SLOT_DOMN
GROUP BY domn,
slot
) AS sd2 ON sd2.domn = sd1.domn
WHERE sd1.slot <> sd2.slot
GROUP BY sd1.slot,
sd2.slot[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources