*Complex* Grouping In View - Help Needed

Hello,

I am having difficulty to find the right SQL query to create a View as i illustrate below.

Senario:

Criteria Table






Idn

Key1

Key2

Key3

TagId


1

A

C

B

100


2

A

NULL

B

200


3

B

D

NULL

300

Data Table






DataId

Key1


1

A


2

B

SubData Table






SubDataId

DataId

Key2

Key3


1

1

C

B


2

1

Z

B


3

1

X

B


4

2

D

Z

And below is my expected View:






TagId

Key1

Key2

Key3


100

A

C

B


200

A

Z

B


200

A

X

B


300

B

D

Z

I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.

I would really appreciate if anyone could help me to make if faster or point me where i did wrong.

Below is sample T-SQL that which i can illustrate my situation:

set nocount on
create table #Criteria
(
idn int
,Key1 char(1)
,Key2 char(1)
,Key3 char(1)
,TagId int
)

create table #Data
(
DataId int
,Key1 char(1)
)

create table #SubData
(
SubDataId int
,DataId int
,Key2 char(1)
,Key3 char(1)
)

insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', 'C', 'B', 100)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', NULL, 'B', 200)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'B', 'D', NULL, 300)

insert #Data(DataId, Key1)
values (1, 'A')
insert #Data(DataId, Key1)
values (2, 'B')

insert #SubData(SubDataId, DataId, Key2, Key3)
values (1, 1, 'C', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (2, 1, 'Z', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (3, 1, 'X', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (4, 2, 'D', 'Z')

select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId


/** here is the query logic i used in the view **/
select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

drop table #Criteria, #Data, #SubData



P/s: i urgently need your feedback on this one.

Thank You!!!

sibikos@hotmail.com