partial lookup performance

Question

Hi All,

I have implemented partial lookup in fact table due to selection lookup having the validation in between the values of the lookup table.

query as:

SELECT X1, X2, X3
FROM TABLE1
WHERE X1 = ?
AND ? BETWEEN X2 AND X3 

--- WHERE ? ARE VARIABLES IN THE modify the SQL statement in the advanced tab of lookup.

I have calculated the Cache size as per the standards.

THE LOOKUP IS SETTING FOR Ignore failure so I will get matched and NULL for not matched columns as output.

This partial lookup is running too slow, I could say for 100K records it is taking more than 1 hr time.

Is there any way to improve the performance of partial lookup.

SELECT [ISP_SK]     
      ,[ISP_SOURCE_ID]
      ,[ISP_ID]
      ,[ISP_SET_START_DATETIME]
      ,[ISP_SET_END_DATETIME]               
  FROM [DM].[SHARED].[INDIVIDUAL_PROVIDER_DIM]
  WHERE ([ISP_RECORD_EXPIRY_DATETIME] IS NULL
  OR [ISP_RECORD_EXPIRY_DATETIME] = '9999-12-31') 
AND ? BETWEEN [ISP_SET_START_DATETIME]  AND [ISP_SET_END_DATETIME] 
and [ISP_SOURCE_ID] = ? 
AND [ISP_ID] = ?

thanks

prav