I'm seeing a performance issue in one of the sproc that we use in our application. 

It’s a very big sproc and I have narrowed it down to the part where I'm seeing performance issue.

It’s in the where clause ( copied below). Query estimation plan shows this part takes about 80%.

Logic is that `@AssignedToIds` and `@AssignedToRoleIds` can be null, it is it null then we will pull all the records.
Temp tables can have multiple rows. Any help on improving the performance is greatly appreciated.

`#AssignedTo` and `#AssignedToRole` are temp tables.

`#AssignedTo` has only one value in the table and `#AssignedToRole` is empty

SQL:-

```
SELECT DISTINCT TOP 2000 t.Member_Party_PartyId AS Member_Party_PartyId
FROM Task t
WHERE t.IsDeleted = 0
    AND (
        t.DueDate >= @DueStart
        OR @DueStart IS NULL
        )
    AND (
        t.DueDate <= @DueEnd
        OR @DueEnd IS NULL
        )
    AND (
        (
            @FilterType = 'MyPatients'
            AND t.AssignedUserId = @UserId
            )
        OR @FilterType != 'MyPatients'
        )
    AND (@FilterType != 'MyRole')
    AND (
        (
            @FilterType = 'MyGroup'
            AND t.AssignedUserId IN (
                SELECT PartyId
                FROM #OrgMembers
                )
            )
        OR @FilterType != 'MyGroup'
        )
    AND (
        (
            @FilterType = 'Custom'
            AND vpad.Provider IN (
                SELECT PartyId
                FROM #OrgMembers
                )
            )
        OR @FilterType != 'Custom'
        )
    AND (
        (
            @ActiveCase = 1
            AND cases.CaseId IS NOT NULL
            )
        OR @ActiveCase = 0
        )
    AND (
        t.TaskStatusId IN (
            SELECT TaskStatusId
            FROM #TaskStatus
            )
        )
    AND (
        t.TaskCategoryId IN (
            SELECT TaskCategoryId
            FROM #TaskCategory
            )
        OR @TaskCategoryIds IS NULL
        )
    AND (
        t.TaskPriorityId IN (
            SELECT TaskPriorityId
            FROM #TaskPriority
            )
        OR @TaskPriorityIds IS NULL
        )
    AND (
        rm.RegistryId IN (
            SELECT RegistryId
            FROM #Registry
            )
        OR @RegistryIds IS NULL
        )
    AND (
        (
            fg.CareMeasureId IN (
                SELECT CareMeasureId
                FROM #CareMeasure
                )
            AND exclusion.MemberId IS NULL
            )
        OR @CareMeasureIds IS NULL
        )
    AND (
        vpad.OrganizationId IN (
            SELECT OrganizationId
            FROM #Organization
            )
        OR (
            SELECT count(OrganizationId)
            FROM #Organization
            ) = 0
        )
    AND (
        vpad.Provider IN (
            SELECT ProviderId
            FROM #Provider
            )
        OR @ProviderIds IS NULL
        )
    AND (
        cases.CaseTypeId IN (
            SELECT CaseTypeId
            FROM #CaseType
            )
        OR @CaseIds IS NULL
        )
    AND
    --(case when @AssignedToIds  Is Not Null And then t.AssignedUserId in (select AssignedToId from #AssignedTo))
    (
        (
            t.AssignedUserId IN (
                SELECT AssignedToId
                FROM #AssignedTo
                )
            OR (
                @AssignedToIds IS NULL
                AND @AssignedToRoleIds IS NULL
                )
            )
        OR (
            t.AssignedRoleId IN (
                SELECT AssignedRoleId
                FROM #AssignedToRole
                )
            OR (
                @AssignedToRoleIds IS NULL
                AND @AssignedToIds IS NULL
                )
            )
        )
    AND (
        vpad.OrganizationId IN (
            SELECT OrganizationId
            FROM #UserOrgs
            )
        OR (
            (
                SELECT count(OrganizationId)
                FROM #UserOrgs
                ) = 0
            )
        OR (@RoleType <> 'Manager')
        )
    AND (
        (
            mhp.MemberHealthPlanTypeId IN (
                SELECT HealthPlanId
                FROM #HealthPlan
                )
            AND hpds.HierarchyOrder IS NOT NULL
            )
        OR @HealthPlanIds IS NULL
        )
OPTION (RECOMPILE);

```