I would suggest a change to the way that WHERE clause is written.

Since it's a sproc why not put all the profile id's into a temp table (or a table variable if the number is low - like around a couple of 100).

Let's say you go the temp table route

Code:
Create #ProfileList (ProfileId asdf)
Insert into #ProfileList
   Select ProfileId From Profile
	WHERE ([Profile].LastModifiedDate BETWEEN @startDate AND @endDate AND [Profile].[SubscriptionId] = @subscriptionId)

Union -- do not use UNION ALL as we want to get rid of duplicate profile id's

   SELECT MasterAgencyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)

Union

   SELECT MasterCompanyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)

Union

   SELECT TransactionalAgencyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)


Union

   SELECT TransactionalCompanyProfileId FROM  meetinginfo WITH (NOLOCK) WHERE  EntityTypeId=1 AND EntityId IN(
    SELECT RfpId FROM RfpAuditLog WITH (NOLOCK) WHERE SubscriptionId = @subscriptionId 
    AND EffectiveDate BETWEEN @startDate AND @endDate)
Then your actual query would come from this table

Code:
SELECT   
 [Profile].[ProfileId]  
    ,[Profile].[ProfileTypeId]  
    ,[Profile].[SicCodeId]  
    ,[Profile].[CompanyName]  
    ,[Profile].[AddressLine1]  
    ,[Profile].[AddressLine2]  
    ,[Profile].[AddressLine3]  
    ,[Profile].[url]  
    ,[Profile].[PhoneNumber]  
    ,[Profile].[FaxNumber]  
    ,[Profile].[email]  
    ,[Profile].[City]  
    ,[Profile].[StateProvince]  
    ,[Profile].[PostalCode]  
    ,[Profile].[Commission]  
    ,[Profile].[ExternalSicCode]  
    ,[Profile].[CountryId]  
    ,[Profile].[ExternalCountry]  
    ,[Profile].[CreateDate]  
    ,[Profile].[LastModifiedDate]  
    ,[Profile].[IsCommissionable]  
    ,[Profile].[ExternalProfileId]
    ,[SetupValue].[Name] AS SicCode 
    ,[Profile].[IATANumber] 
  FROM #ProfileList PL
  Left Join [Profile] PR on PR.ProfileID=PL.ProfileId WITH (NOLOCK)  
 LEFT JOIN [SetupValue] WITH (NOLOCK) --Added as per CR0050710  
   ON [Profile].SicCodeId = [SetupValue].SetupValueId
If you have a problem with temp tables that whole select/union query could go into a DERIVED QUERY and come FROM that. Would basically do the exact same thing.

This would definately get rid of that heavy WHERE clause - and you can also tell if the SELECT/UNION is still slow and further fix it up (it does have it's own IN () sub-query issue that could be put first into another temp table for a staged build)