Indexed Views with Self-Joins

I realize that SQL Server doesn't support indexed views that have
self-joins, but are there any reasonable work-arounds to this?
In the following view definition, the LawLink table contains IDs for a
lawyer/lawfirm pair, and the Profile table contains one record for each
lawyer and one record for each law firm. (ProfileID is the primary key of
the Profile table, and ProfileIDs are unique across all lawyers and law
firms.)
CREATE VIEW EntityLawLink WITH SCHEMABINDING
AS
SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
COUNT_BIG(*) AS Frequency
FROM dbo.LawLink LL
INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
GROUP BY L.EntityID, F.EntityID
GO
The problem is that SQL Server sees this as a self-join because the Profile
table is referenced twice. When I issue the command:
CREATE UNIQUE CLUSTERED INDEX Test
ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
GO
SQL Server responds: Index cannot be created ... because the view contains
a self-join on 'dbo.Profile'.
I don't really understand why SQL Server has this restriction. I suppose
that the Profile table could be seen as indirectly referencing itself
through the LawLink table, but I had assumed that the self-referencing
restriction had to do with a table directly referencing itself. Why should
SQL Server care that the table is used twice?
If I split the Profile table into two tables (one for lawyers and one for
law firms) then everything works fine. Unfortunately, that's not practical
in this circumstance. Can anyone suggest any other alternatives?
Thanks,
MarkMark,
Yes, I was disappointed as well to see that no-self join included not
joining to the same table twice. (!!!)
Here is a possible workaround:
1. You will need another table, perhaps LawFirmProfile.
2. You do not want or need that table for another other purpose than this
indexed view.
3. Create UPD,INS,DEL triggers for the Profile table.
All these triggers will do is keep the LawFirmProfile table up-to-date
with what is in Profile.
4. Build your indexed view with a join to Profile (for lawyers) and
LawFirmProfile (for firms.)
Perhaps another question to ask is whether the indexed view is really giving
you enough boost to make it worth fooling with. We backed off on indexed
view (not all of them) once we understood the limitations.
FWIW,
Russell Fields
"Mark Pauker" <mpauker@.optonline.net> wrote in message
news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
> I realize that SQL Server doesn't support indexed views that have
> self-joins, but are there any reasonable work-arounds to this?
> In the following view definition, the LawLink table contains IDs for a
> lawyer/lawfirm pair, and the Profile table contains one record for each
> lawyer and one record for each law firm. (ProfileID is the primary key of
> the Profile table, and ProfileIDs are unique across all lawyers and law
> firms.)
> CREATE VIEW EntityLawLink WITH SCHEMABINDING
> AS
> SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
> COUNT_BIG(*) AS Frequency
> FROM dbo.LawLink LL
> INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
> INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
> GROUP BY L.EntityID, F.EntityID
> GO
> The problem is that SQL Server sees this as a self-join because the
Profile
> table is referenced twice. When I issue the command:
> CREATE UNIQUE CLUSTERED INDEX Test
> ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
> GO
> SQL Server responds: Index cannot be created ... because the view
contains
> a self-join on 'dbo.Profile'.
> I don't really understand why SQL Server has this restriction. I suppose
> that the Profile table could be seen as indirectly referencing itself
> through the LawLink table, but I had assumed that the self-referencing
> restriction had to do with a table directly referencing itself. Why
should
> SQL Server care that the table is used twice?
> If I split the Profile table into two tables (one for lawyers and one for
> law firms) then everything works fine. Unfortunately, that's not
practical
> in this circumstance. Can anyone suggest any other alternatives?
> Thanks,
> Mark
>|||Thanks for your thoughts. I contemplated this, but having a trigger to
update a table that's used in an indexed view will likely lead to
unacceptable performance bottlenecks.
I also looked into physically separating the Profile table into 2 separate
tables and using a UNION ALL view to create a logical Profile table. The
problem is that the performance characteristics of the Profile view were not
acceptable. (I can't create a partitioned view at this level, so most
queries take at least twice as long to complete.)
Regarding the question of whether or not the indexed view is worth it in
this case, the query appears to run about 30 times faster using the view.
Well worth spending some extra time to see if there's a reasonable
workaround. Of course, I was hoping that there might simply be a different
way of creating the view without having to build temporary constructs.
-- Mark
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23IiXjzIUEHA.712@.TK2MSFTNGP11.phx.gbl...
> Mark,
> Yes, I was disappointed as well to see that no-self join included not
> joining to the same table twice. (!!!)
> Here is a possible workaround:
> 1. You will need another table, perhaps LawFirmProfile.
> 2. You do not want or need that table for another other purpose than this
> indexed view.
> 3. Create UPD,INS,DEL triggers for the Profile table.
> All these triggers will do is keep the LawFirmProfile table
up-to-date
> with what is in Profile.
> 4. Build your indexed view with a join to Profile (for lawyers) and
> LawFirmProfile (for firms.)
> Perhaps another question to ask is whether the indexed view is really
giving
> you enough boost to make it worth fooling with. We backed off on indexed
> view (not all of them) once we understood the limitations.
> FWIW,
> Russell Fields
> "Mark Pauker" <mpauker@.optonline.net> wrote in message
> news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
> > I realize that SQL Server doesn't support indexed views that have
> > self-joins, but are there any reasonable work-arounds to this?
> >
> > In the following view definition, the LawLink table contains IDs for a
> > lawyer/lawfirm pair, and the Profile table contains one record for each
> > lawyer and one record for each law firm. (ProfileID is the primary key
of
> > the Profile table, and ProfileIDs are unique across all lawyers and law
> > firms.)
> >
> > CREATE VIEW EntityLawLink WITH SCHEMABINDING
> > AS
> > SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
> > COUNT_BIG(*) AS Frequency
> > FROM dbo.LawLink LL
> > INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
> > INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
> > GROUP BY L.EntityID, F.EntityID
> > GO
> >
> > The problem is that SQL Server sees this as a self-join because the
> Profile
> > table is referenced twice. When I issue the command:
> >
> > CREATE UNIQUE CLUSTERED INDEX Test
> > ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
> > GO
> >
> > SQL Server responds: Index cannot be created ... because the view
> contains
> > a self-join on 'dbo.Profile'.
> >
> > I don't really understand why SQL Server has this restriction. I
suppose
> > that the Profile table could be seen as indirectly referencing itself
> > through the LawLink table, but I had assumed that the self-referencing
> > restriction had to do with a table directly referencing itself. Why
> should
> > SQL Server care that the table is used twice?
> >
> > If I split the Profile table into two tables (one for lawyers and one
for
> > law firms) then everything works fine. Unfortunately, that's not
> practical
> > in this circumstance. Can anyone suggest any other alternatives?
> >
> > Thanks,
> > Mark
> >
> >
>