September 1, 2015 at 10:13 am
Hello, I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible. I know I can do this with a sproc but I'm hoping to get some more insight here...
I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView
AS
WITH ContactCTE AS(
SELECT Col1,
Col2,
TenantId
FROM Contacts
INNER JOIN Numbers ON Contacts.ContactId = Numbers.ContactId
--WHERE Contacts.TenantId = 50
GROUP BY Col1,
Col2,
TenantId.
)
SELECT Col1, Col2, Contacts.TenantId
FROM Contacts INNER JOIN ContactCTE ON Contacts.ContactId = ContactCTE.ContactId
September 1, 2015 at 10:27 am
Instead of a view, look into creating an inline table-valued function. That can function essentially as a parameterized view and is very efficient at doing so.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 1, 2015 at 11:43 am
The problem is likely to be the Group By that you have there. I don't know why it's there, because there's no aggregations.
If the group by is removed, and someone queries the view with a parameter, the query optimiser will (unless the view is more complex than you've shown) be able to push the predicate right down to the table.
The group by prevents that, the grouping has to be done and then the filtering, which will be slow.
If the group by is necessary for some aggregation you didn't show, then go with Scott's suggestion of an in-line table-valued function (but make sure it's an in-line one you write), assuming Entity Framework's capable of passing a parameter to a function instead of filtering, which I suspect it may not be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2015 at 11:16 am
Here is the exact CTE I am using
WITH ContactCTE AS(
SELECT Contacts.ContactId
, COUNT(DISTINCT(Plans.PlanId)) AS PlanCount
, MAX(Plans.CreatedOn) As LastDatePlanAdded
, CompanyNames.cnName AS CompanyName
, Contacts.FirmId
FROM Contacts
INNER JOIN PlanContactLink ON Contacts.contactID = PlanContactLink.pclContactID
LEFT JOIN Plans ON PlanContactLink.pclPlanID = Plans.planID
LEFT JOIN ContactListMembers ON Contacts.contactID = ContactListMembers.ContactId AND ContactListMembers.IsPrimary = 1
LEFT JOIN CompanyNames ON CompanyNames.companyNameID = Contacts.conCompanyName
--WHERE Contacts.firmID = 50
GROUP BY Contacts.ContactId
, CompanyNames.cnName
, Contacts.FirmId
)
That commented filter, which I was hoping the query optimizer would apply automatically, is the difference between the view executing very quickly. So I'm not sure that the group by is the issue.
From there the CTE is just joined into my view like so:
SELECT *
FROM Contacts
INNER JOIN ContactCTE ON Contacts.ContactId = ContactCTE.ContactId
And then Entity Framework will SELECT FROM ViewName Where Contacts.FirmId = 50. FirmId is the 'TenantId' I described earlier that is in every table.
I will look into the inline table-valued function. It looks like that may be a solution if I can make it work with EF. I am still curious why the view doesn't filter as expected tho. If you have any more ideas please let me know.
September 2, 2015 at 1:24 pm
You could also try this, just need to add the additional columns you need from the Contacts table.
SELECT
con.ContactId,
COUNT(DISTINCT(pln.PlanId)) OVER (PARTITION BY con.ContactId, cn.cnName, con.FirmId) AS PlanCount,
MAX(pln.CreatedOn) OVER (PARTITION BY con.ContactId, cn.cnName, con.FirmId) AS LastDatePlanAdded,
cn.cnName AS CompanyName,
con.FirmId,
-- con.<colname1>, con.<colname2>, ...
FROM
dbo.Contacts con
INNER JOIN dbo.PlanContactLink pcl
ON con.contactID = pcl.pclContactID
LEFT JOIN dbo.Plans pln
ON pcl.pclPlanID = pln.PlanID
LEFT JOIN dbo.ContactListMembers clm
ON con.contactID = clm.ContactId AND
clm.IsPrimary = 1
LEFT JOIN dbo.CompanyNames cn
ON cn.companyNameID = cons.conCompanyName
--WHERE con.FirmID = 50
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply