I have a beast of a table which is in the outer query having more than 40million rows. I am doing an incremental/delta load picking up data for only 6 days but this query for some odd reason is taking more than 25min, despite the fact that its only bringing in 6 days worth of data. The query is as follows:

```
        SELECT
        ActivityId,
        ActivityPartyId,
        ISNULL(AddressUsed,'*Unknown*') As AddressUsed,
        isnull(Cast(IsPartyDeleted As INT),-9) As IsPartyDeleted,
        Isnull(ParticipationTypeMask,-9) as ParticipationTypeMask,
        PartyId,
        ISNULL(PartyIdName,'*Unknown*') As PartyIdName,
        Isnull(PartyObjectTypeCode,0) as PartyObjectTypeCode
        from ActivityPartyBase

        INNER JOIN 
        (
        SELECT activityid AS apid
        from ActivityPointerBase

        WHERE
        CreatedOn BETWEEN   DATEADD(dd, DATEDIFF(dd,2,getdate()), 0) AND  DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

         or

        ModifiedOn BETWEEN   DATEADD(dd, DATEDIFF(dd,6,getdate()), 0) AND  DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

        ) sub
        ON   ActivityId = sub.apid

```

The Result the subquery is bringing back is only 80,000 rows which is rather small compare to do an inner join to bring back from the outer table.

I was wondering if there is a way to improve the performance of this query at all or create an index in one of the columns. The activityPointerBase table has an clustered index on the activityId but the ActivityParty Table has a clustered index on the ActivityPartyId which are different columns.

Can anyone please advise the best way to improve this query and run time.