Need some serious help with self join issue.

Question

Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!!

The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html

The view I want to apply this work around to is:

create VIEW vw_lookup_test
WITH SCHEMABINDING
AS
select
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm [owner],
t.circt_cstdn_nm [tech],
dvc.circt_nm,
data_orgtn_yr 
from 
((dbo.dvc 
join dbo.circt 
on dvc.circt_nm = circt.circt_nm) 
join dbo.circt_cstdn o
on circt.circt_cstdn_user_id = o.circt_cstdn_user_id)
join dbo.circt_cstdn t
on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm,
t.circt_cstdn_nm,
dvc.circt_nm,
data_orgtn_yr 
go

Any help would be greatly apreciated!!!

Thanks so much in advance!

EDIT : So I found that this will also work. Notice that I join to the table once in the first indexed view, and the second time in teh second non-indexed view.

alter VIEW vw_lookup_owner_test2
WITH SCHEMABINDING  
AS 
select
count_big(*) as [countAll],
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
circt_cstdn_nm,
data_orgtn_yr,
dvc.circt_cstdn_user_id
from dbo.dvc 
join dbo.circt
on dvc.circt_nm = circt.circt_nm
join dbo.circt_cstdn o
on circt.circt_cstdn_user_id = o.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
circt_cstdn_nm,
data_orgtn_yr,
dvc.circt_cstdn_user_id
go

and

CREATE UNIQUE CLUSTERED INDEX [idx_vw_lookup_owner2_test1] ON [dbo].[vw_lookup_owner_test2] 
(
    [awc_txt] ASC,
    [city_nm] ASC,
    [str_nm] ASC,
    [stru_no] ASC,
    [circt_nm] ASC,
    [circt_cstdn_nm] ASC,
    [data_orgtn_yr] ASC,
    [circt_cstdn_user_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

and

create view vw_lookup_dvc_loc
as
select
awc_txt,
city_nm,
str_nm,
stru_no,
circt_nm,
o.circt_cstdn_nm as [owner],
--o.circt_cstdn_user_id,
t.circt_cstdn_nm as tech,
data_orgtn_yr
from vw_lookup_owner_test2 o With (NOEXPAND)
join circt_cstdn t
on o.circt_cstdn_user_id = t.circt_cstdn_user_id
group by
awc_txt,
city_nm,
str_nm,
stru_no,
circt_nm,
o.circt_cstdn_nm,
data_orgtn_yr,
t.circt_cstdn_nm
--o.circt_cstdn_user_id

I can then create additon indexes on the first view as I wish. I'm not sure if this solution (or the workaround for that matter) will actually speed up preformance but i'll let you know.

Answer 1

I think this JOIN syntax is horrible and originated from MS Access. ugh. I recommend you use:

select
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm [owner],
t.circt_cstdn_nm [tech],
dvc.circt_nm,
data_orgtn_yr 

-- HERE
from dbo.dvc
join dbo.circt on (dvc.circt_nm = circt.circt_nm) 
join dbo.circt_cstdn o on (circt.circt_cstdn_user_id = o.circt_cstdn_user_id)
join dbo.circt_cstdn t on (dvc.circt_cstdn_user_id = t.circt_cstdn_user_id)

group by
awc_txt,
city_nm,
str_nm,
stru_no,
o.circt_cstdn_nm,
t.circt_cstdn_nm,
dvc.circt_nm,
data_orgtn_yr 

This syntax is cleaner, more comprehensible and is recognized in SQL Server, Firebird, Oracle, MySQL and much others. Now you can see better the relations between "tables". When you join the same "table" two or more times, you need to alias each one. On one line "circt_cstdn" is aliased as "o". On other line, "circt_cstdn" is aliased as "t".

I recommend use of LEFT JOIN or INNER JOIN instead JOIN.

Answer 2

Here's what I got from the blogpost

• Let's say you want to join 2 times on dbo.circt_cstdn i.e. you want something like

           owner       tech
  rowA     a.nm        b.nm
  ...
  

• Instead of getting the values into 2 columns you get it into 2 rows (2 for each row above) and add an additional column to say which row is for which column. Note that row 1.1 and row 1.2 have the same data (except for the name and for columns)

           name   for
  row1.1   nm     owner
  row1.2   nm     tech
  ...
  

• Then you pivot on max of name column for owner and tech. Note - the max function is just to trick the PIVOT (which requires some aggregate function), you could use any aggregate function that returns the same value if there is only one record owner tech row1 nm nm ...

Now if we do this for your query

1. Create a table d, like this one

    i
    1
    2
   

2. Cross join the first part of your query with this

   SELECT 
        count_big(*) as [count_all], 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr
   FROM  
        dbo.dvc  
        INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
        CROSS JOIN dbo.d  
   GROUP BY
        awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, d.i
   

3. Now let's use the row for owner if D.i is 1, and the tech if D.i is 2

   SELECT 
        count_big(*) as [count_all], 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr,
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END
   FROM  
        dbo.dvc  
        INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
        CROSS JOIN dbo.d 
   GROUP BY
        awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, 
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END  
   

4. Now add the nm column. To get the name you join circt_cstdn with circt if it's an owner row (d.i = 1), and with dvc if it's a tech row (d.i = 2). Note - I tried a shortcut here by putting this in the join condition. If it doesn't work try the blog post way (do a join on circt.circt_cstdn_user_id OR dvc.circt_cstdn_user_id, and then use the WHERE clause to filter out)

   SELECT 
        count_big(*) as [count_all], 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr,
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END as PersonType,
        circt_cstdn_nm
   FROM  
        dbo.dvc  
        INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm
        CROSS JOIN dbo.d 
        INNER JOIN dbo.circt_cstdn on circt_cstdn_user_id = 
             CASE
                  WHEN d.i = 1 THEN circt.circt_cstdn_user_id
                  WHEN d.i = 2 THEN dvc.circt_cstdn_user_id
             END
   GROUP BY
        awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, 
        Case 
            WHEN d.i = 1 THEN 'Owner'
            WHEN d.i = 2 THEN 'Tech'
        END,
        circt_cstdn_nm
   

5. Create a view using that and create the index

   create VIEW vw_lookup_test_imed
   WITH SCHEMABINDING 
   AS
       <<query above>>  
   GO
   
   
   spell to create INDEX
   

6. Now you PIVOT to convert the PersonType column to Owner and Tech columns

   SELECT 
        count_all, 
        awc_txt, 
        city_nm, 
        str_nm, 
        stru_no, 
        dvc.circt_nm, 
        data_orgtn_yr,
        [Owner], 
        [Tech] 
   FROM 
   ( 
        SELECT 
             count_all, 
             awc_txt, 
             city_nm, 
             str_nm, 
             stru_no, 
             dvc.circt_nm, 
             data_orgtn_yr,
             PersonType,
             circt_cstdn_nm
        FROM dbo.vw_lookup_test_imed WITH (NOEXPAND) 
   ) src 
   PIVOT 
   ( 
        MAX(circt_cstdn_nm) 
        FOR PersonType IN ([Owner], [Tech]) 
   ) pvt 
   

If there are syntax errors (there's bound to be lots cause I don't have access to a database right now) let me know.