April 4, 2015 at 10:57 am
Consider following code:
SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really help performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:
Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".
I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.
Any other ideas on how to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.
Thanks in advance.
Mike Byrd
Mike Byrd
April 4, 2015 at 11:30 am
April 4, 2015 at 12:34 pm
Is all the data in these two tables 'active'? Is there any way you could partition the tables either the the old fashioned way by creating a history table and a current table or with a partition function(Enterprise version only) or with Partitioned Views.
If you post some of your current queries and index DDL (and hopefully some query plans) I'm sure someone could help advise on the current index strategy.
April 6, 2015 at 8:10 am
Mike Byrd (4/4/2015)
Consider following code:SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
The only thing the second JOIN does is validating that a counterpart of EntityAssociation.EntityID2 does exist in Entity. Provided that Entity.ID is PK and the FK EntityAssociation.EntityID2 -> Entity.ID is in place it's save to rewrite the code as
SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entity e1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
AND ea.EntityID2 IS NOT NULL --valid user reference
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply