Filtering Common Table Expression within View

  • 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

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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