Optimize a Sql subquery containing multiple inner joins and aggregate functions

I have a select statement which is infact a subquery within a larger select statement built up programmatically. The problem is if I elect to include this subquery it acts as a bottle neck and the whole query becomes painfully slow.

An example of the data is as follows:

Payment
.Receipt_no|.Person |.Payment_date|.Type|.Reversed| 
          2|John    |01/02/2001   |PA   |         |
          1|John    |01/02/2001   |GX   |         |
          3|David   |15/04/2003   |PA   |         |
          6|Mike    |26/07/2002   |PA   |R        |
          5|John    |01/01/2001   |PA   |         |
          4|Mike    |13/05/2000   |GX   |         |
          8|Mike    |27/11/2004   |PA   |         |
          7|David   |05/12/2003   |PA   |R        |
          9|David   |15/04/2003   |PA   |         |

The subquery is as follows :

select Payment.Person, 
Payment.amount 
from Payment
inner join (Select min([min_Receipt].Person) 'Person',
   min([min_Receipt].Receipt_no) 'Receipt_no' 
   from Payment [min_Receipt] 
   inner join (select min(Person) 'Person', 
      min(Payment_date) 'Payment_date' 
      from Payment
      where Payment.reversed != 'R' and Payment.Type != 'GX' 
      group by Payment.Person) [min_date] 
   on [min_date].Person= [min_Receipt].Person and [min_date].Payment_date = [min_Receipt].Payment_date 
   where [min_Receipt].reversed != 'R' and [min_Receipt].Type != 'GX' 
   group by [min_Receipt].Person) [1stPayment] 
on [1stPayment].Receipt_no = Payment.Receipt_no

This retrieves the first payment of each person by .Payment_date (ascending), .Receipt_no (ascending) where .type is not 'GX' and .Reversed is not 'R'. As Follows:

Payment
.Receipt_No|.Person|.Payment_date
          5|John   |01/01/2001
          3|David  |15/04/2003
          8|Mike   |27/11/2004

I am unable to move the subquery out to a temporary table as temporary tables are simply not supported within the programming language used by my application.

Edit : Incorrec

I see that in your question you said:


"I am unable to move the subquery out to a temporary table as temporary tables are simply not supported within the programming language used by my application."

But, have you considered calling a stored procedure instead? Is this even an option, considering the limitations with the programming language?

If this is a viable option, you could simply have the results of your subquery inserted into a temp table transparently & encapsulate all the logic in the stored procedure.

Edit

I got to thinking about this some more, and perhaps the columns that you're using in your JOIN condition are of different collations. While this will usually result in a specific error message, there may be some implicit collation coversion occurring instead (see: MSDN: Collation Precedence (Transact-SQL)) between the sub-query & the data being joined.

Here are a few links about collation that might be useful to you:

-
Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS

-
Collation Hell (Part 1)

-
SQL SERVER – Find Collation of Database and Table Column Using T-SQL

-
SQL SERVER – Change Collation of Database Column – T-SQL Script

Also, you may be able to trick your programming language into using a temp table with syntax like this:

SELECT *
FROM tempdb..#MyTempTable


Just keep in mind that sometimes the temp database has a different collation then the data you're working with too, in which case you'll need to explicitly convert the data to/from each collation.

StackExchange Code Review Q#18321, answer score: 2