sql server - How Can I speed up Multiple Left Outer Join, -

sql server - How Can I speed up Multiple Left Outer Join, -

this ms-sql query

i have utilize lots of left inner join.

numberofrow = 300,000 rows

result time 4sec.

how can speed query?.

pk 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 @location (regionid, regiontype, subclass, regionname, regionnamelong, parentregionid) select origin.regionid regionid ,origin.regiontype regiontype ,origin.subclass subclass ,refer.regionname regionname ,refer.regionnamelong regionnamelong ,origin.parentregionid parentregionid location_en_us origin inner bring together location_ko_kr 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 @location en_1 left outer bring together @location en_2 on en_1.parentregionid = en_2.regionid left outer bring together @location en_3 on en_2.parentregionid = en_3.regionid left outer bring together @location en_4 on en_3.parentregionid = en_4.regionid left outer bring together @location en_5 on en_4.parentregionid = en_5.regionid left outer bring together @location en_6 on en_5.parentregionid = en_6.regionid inner bring together regiontype rt on en_1.regiontype = rt.typecode , rt.languagecode = 'en_us' inner bring together subclass sc on en_1.subclass = sc.typecode , sc.languagecode = 'en_us' en_1.regionnamelong '%seuol%'

this utilize hierarchyid, lowest 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 regionname1, (select regionname location_en_us level = location.level.getancestor(1)) level2, (select regionname location_en_us level = location.level.getancestor(2)) level3, (select regionname location_en_us level = location.level.getancestor(3)) level4, (select regionname location_en_us level = location.level.getancestor(4)) level5, (select regionname location_en_us level = location.level.getancestor(5)) level6 location_en_us location regionnamelong '%seoul%'

should utilize hierarhyid tecnique operating tree-like structures.

build index parentregionid, regionid columns.

also, none of listed bring together tables picked query result set.

best wishes!

sql-server left-join