Q:

Optimize SQL query with and or and structure in WHERE clause

I have a function that has to convert currencies based on a conversion rate retrieved from a a table with historic values. It takes four parameters (@to_curr, @from_curr, @trans_date, @gl_cmp) adn returns the exchange rate.
The table structure is 

currencypk int primary key
gl_crcnv_bdate datetime
gl_crcnv_edate datetime
gl_crcnv_rate float
gl_cmp_key char(30) (we have multiple companies this identifies each one)
gl_CRNCY_TO char(30)
gl_CRNCY_FROM char(30)

Below is my code and when I insert this in a function it causes a MASSIVE increase in execution time. I have a pretty good idea where the bottle neck is in my function but I am stuck on how to rewrite it. The reason why I have a ugly where clause with a (and) or (and) setup is because not every relationship is reversed.  
For example there are records with a conversion rate from USD to GBP but there doesn't exist one at all with GBP to USD. That fact accounts for the case statement in the SELECT, to get the actual conversion that doesn't exist in the table. The isnull is in case it returns nothing, so it uses 1
Any help is greatly appreciated.
SELECT 
    isnull((SELECT convert(decimal(5,4),  
                   case 
                      when @to_curr <> gl_CRNCY_TO 
                         then (1/gl_crcnv_rate)
                         else gl_crcnv_rate
                    end)
            FROM 
               [TABLE]
            WHERE 
               gl_cmp_key = @gl_cmp   
               AND ((gl_CRNCY_TO = @from_curr AND gl_CRNCY_FROM = @to_curr) 
                    OR (gl_CRNCY_TO = @to_curr AND gl_CRNCY_FROM = @from_curr))
               AND @trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate), 1)

A:

You could try:
SELECT isnull(SELECT convert(decimal(5,4),  gl_crcnv_rate)
              FROM (select 1/gl_crcnv_rate as gl_crcnv_rate
                    from [TABLE]
                    WHERE gl_cmp_key = @gl_cmp AND 
                          gl_CRNCY_TO = @from_curr AND 
                          gl_CRNCY_FROM = @to_curr AND 
                          @trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate
                    UNION
                    select gl_crcnv_rate
                    from [TABLE]
                    WHERE gl_cmp_key = @gl_cmp AND 
                          gl_CRNCY_TO = @to_curr AND 
                          gl_CRNCY_FROM = @from_curr AND 
                          @trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate
                    ) sq,
              1)

This assumes that if both possible conversion records exists then the conversion factor will evaluate to the same value, and that there is an index that the query can use.

