views:

118

answers:

2

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.

A: 

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.

Nelson H C Nepomuceno
Two things. First I totally agree with you: I ALWAYS use left joins. However you cannot index a view with left joins, and I tested it and I get the same results either way.Secondly, I think What you just posted is almost identical to what I posted in my original post... I did use the aliases o and t...
kralco626
And I take great offence that you would imply that I learned by database knowlege from MSAccess! I hate MsAccess. (The sql language used has stupid limitations and sucks). And "INNER JOIN" is exactly the same as "JOIN"...
kralco626
+1  A: 

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.

potatopeelings
is this last query you posted another view? because my understanding is that I can't index a view that references another view... Or am i to index the first view? and use this second one...?
kralco626
O.K. I reread you post and I'm pretty sure I would index the first view. But how would i index that first view?
kralco626
Alright! I got the first view created. created a unique clustered index over 8 cols, I then ran the select statement from the second view and I got 300486 rows rather than 299925, which is how many I get when I run my original statement. lol you preformed magic :) you created 561 rows... Any ideas?
kralco626
ow! let's check a couple of things. 1. How many rows does the new vw_lookup_test_imed return - 2 x 300486 or 2 x 299925. 2. can you do a <new one> EXCEPT <original query>, and check if there are any nulls (or userids not in circt_cstdn) in there? If the original query does an INNER JOIN, rows will be excluded if either the Owner or Tech id is missing. I'm guessing a pivot would return a row but put a blank/null for the missing Owner or Tech.
potatopeelings
Alright. 1. So the query returns 599222 rows (2 X 299611) The pivoit query returns 300486 rows. (Yes I copy and pasted what you wrote above exactly as was except for syntax fixs). 2. There are FK contraints on all the joins, and there are no nulls in any of the circt_cstdn_user_id, circt_cstdn_nm or circt_nm cols. All querys use the join key word "join" no left,inner,outer.... I must say "join" in order to index the view so thats what i did. Left join and join return the same number of rows anyways...
kralco626
ooh... that means there's a problem in the view query AND the pivot query. i'm worse at this than i think i am. On the offchance your latest EDIT didn't work, here's a couple more things to localize the error. Does the query in 2. (in the Answer) return 599850 rows? if it doesn't take out the CROSS JOIN and check if it returns 299925. Logically, it should. Then check if 3. returns the same number of rows as 2. (it should) = 599850.
potatopeelings
Just to add - I'm just fishing with the above checks. I don't have a clue why it's happening :-)
potatopeelings