How Can I speed up Multiple Left Outer Join,

Question

this is MS-SQL Query

I have use Lots of Left Inner Join.

NumberOfRow = about 300,000 Rows

Result time is about 4sec.

How can I speed up this query?.

PK is Id, Indexed RegionID

DECLARE @Location TABLE
        (
        Id int IDENTITY(1,1) PRIMARY KEY not null,
        RegionID int ,
        RegionType int ,
        SubClass int ,
        RegionName nvarchar(255) ,
        RegionNameLong nvarchar(512) ,
        ParentRegionID int
)

INSERT INTO @Location (RegionID, RegionType, SubClass, RegionName, RegionNameLong, ParentRegionID)
SELECT ORIGIN.RegionID AS RegionID
    ,ORIGIN.RegionType AS RegionType
    ,ORIGIN.SubClass AS SubClass
    ,REFER.RegionName AS RegionName
    ,REFER.RegionNameLong AS RegionNameLong
    ,ORIGIN.ParentRegionID AS ParentRegionID
FROM Location_en_US AS ORIGIN
    INNER JOIN Location_ko_KR AS REFER ON ORIGIN.RegionID = REFER.RegionID

SELECT
    TOP 10
    EN_1.RegionID, EN_1.RegionName, EN_2.RegionID, EN_2.RegionName, EN_3.RegionID, EN_3.RegionName, EN_4.RegionID, EN_4.RegionName, EN_5.RegionID, EN_5.RegionName, EN_6.RegionID, EN_6.RegionName
FROM @Location      AS EN_1
    LEFT OUTER JOIN @Location AS EN_2 ON EN_1.ParentRegionID = EN_2.RegionID
    LEFT OUTER JOIN @Location AS EN_3 ON EN_2.ParentRegionID = EN_3.RegionID
    LEFT OUTER JOIN @Location AS EN_4 ON EN_3.ParentRegionID = EN_4.RegionID
    LEFT OUTER JOIN @Location AS EN_5 ON EN_4.ParentRegionID = EN_5.RegionID
    LEFT OUTER JOIN @Location AS EN_6 ON EN_5.ParentRegionID = EN_6.RegionID
    INNER JOIN RegionType       AS RT ON EN_1.RegionType = RT.TypeCode AND RT.LanguageCode = 'en_US'
    INNER JOIN SubClass         AS SC ON EN_1.SubClass = SC.TypeCode  AND SC.LanguageCode = 'en_US'
WHERE   EN_1.RegionNameLong LIKE '%SEUOL%'

this is use hierarchyid, but lowest then left outer join

CREATE TABLE dbo.Location_en_US(
    Id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Level hierarchyid NOT NULL,
    RegionID int NOT NULL,
    RegionType int NOT NULL,
    RelativeSignificance nvarchar(3) NULL,
    SubClass int NULL,
    RegionName nvarchar(255) NOT NULL,
    RegionNameLong nvarchar(512) NOT NULL,
    ParentRegionID int NULL,
    CreatedAt datetime2 NULL DEFAULT (getdate()),
)

SELECT RegionName AS RegionName1, 
(SELECT RegionName FROM Location_en_US WHERE Level = Location.Level.GetAncestor(1)) AS Level2,
(SELECT RegionName FROM Location_en_US WHERE Level = Location.Level.GetAncestor(2)) AS Level3,
(SELECT RegionName FROM Location_en_US WHERE Level = Location.Level.GetAncestor(3)) AS Level4,
(SELECT RegionName FROM Location_en_US WHERE Level = Location.Level.GetAncestor(4)) AS Level5,
(SELECT RegionName FROM Location_en_US WHERE Level = Location.Level.GetAncestor(5)) AS Level6
 FROM Location_en_US AS Location  WHERE RegionNameLong LIKE '%SEOUL%'

for what purpose you have duplicate left join's en_2 - en_6? — xacinay
– xacinay, Commented Jun 23, 2014 at 9:55
@xacinay: It appears that each join is connecting to a parent region with parents becoming NULL when no more parents exist. — Chris Walsh
– Chris Walsh, Commented Jun 23, 2014 at 10:21
@Chris Walsh, I see now - it's 6-level tree condition...Looks like hierarhyId tecnique is appliable here — xacinay
– xacinay, Commented Jun 23, 2014 at 10:37
author, could you comment what the target query output should be? So we can suggest more relevant solutions. What are all these joins for? None of them picked to query result set. — xacinay
– xacinay, Commented Jun 23, 2014 at 10:52
what @xacinay said. As it stands now, you can just remove the left joins, your query will return exactly the same. — Quassnoi
– Quassnoi, Commented Jun 23, 2014 at 10:54

xacinay · Accepted Answer · 2014-06-23 12:57:32Z

1

Should use HierarhyId tecnique for operating tree-like structures.

Build index for ParentRegionID, RegionID columns.

Also, None of the listed join tables are picked to query result set.

Best wishes!

answered Jun 23, 2014 at 12:57

xacinay

9092 gold badges11 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3444535 Over a year ago

i use this one, But Speed it lowest then Left join. Query time is about 9 sec.

Andomar · Accepted Answer · 2014-06-23 10:50:38Z

0

You're joining on ParentRegionID and RegionID. An index on either column might help. You probably have to switch from a table variable @Location to a temporary table #Location before you can add an index.

After adding the index, examine the "Query Plan" from the Query menu to see how this helps.

answered Jun 23, 2014 at 10:50

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Quassnoi · Accepted Answer · 2014-06-23 13:32:22Z

0

Add an index on regionId.

You can't explicitly add indexes to a table variable, so use this trick:

DECLARE @Location TABLE
        (
        Id int IDENTITY(1,1) PRIMARY KEY not null,
        RegionID int ,
        RegionType int ,
        SubClass int ,
        RegionName nvarchar(255) ,
        RegionNameLong nvarchar(512) ,
        ParentRegionID int,
        UNIQUE  (regionId, id)
        )

Also, your left joins don't do anything except probably multiplying the same record. Are you sure your query is correct?

edited Jun 23, 2014 at 13:32

answered Jun 23, 2014 at 11:18

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

5 Comments

user3444535 Over a year ago

this query has a 'incorrect syntax near the keyword 'constraint'' message. and I'm sure this query is correct.

Andomar Over a year ago

@user3444535: You are selecting only columns from EN_1, so the joined tables can only contribute multiplication.

user3444535 Over a year ago

@Andomar Oh I see, SELECT TOP 10 EN_1.RegionID, EN_1.RegionName , It's not correct query.

Quassnoi Over a year ago

@user3444535: please try now, and correct your query in the post.

user3444535 Over a year ago

@Quassnoi query time is about beetween 11 sec and 25 sec, now I changed my query in the post Please check it. Thank you!

Collectives™ on Stack Overflow

How Can I speed up Multiple Left Outer Join,

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related