Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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 : Incorrect statement. Temporary tables are supported and therefore this is a valid option.

Following a post on StackOverflow -

The Query was rewritten as the following.

Query 1.

select min(Payment.Person) 'Person',
    min(Payment.receipt_no) 'receipt_no'
from
    Payment a
where
    a.type<>'GX' and (a.reversed not in ('R') or a.reversed is null)
    and a.payment_date = 
      (select min(payment_date) from Payment i 
      where i.Person=a.Person and i.type <> 'GX' 
      and (i.reversed not in ('R') or i.reversed is null))
group by a.Person

I added this as a subquery within my much larger query, however it still ran very slowly. So I tried rewriting the query whilst trying to avoid the use of aggregate functions and came up with the following.

Query 2.

SELECT
    receipt_no,
    person,
    payment_date,
    amount
FROM
    payment a
WHERE 
    receipt_no IN 
      (SELECT 
           top 1 i.receipt_no 
       FROM 
           payment i 
       WHERE 
           (i.reversed NOT IN ('R') OR i.reversed IS NULL) 
           AND i.type<>'GX' 
           AND i.person = a.person 
       ORDER BY i.payment_date DESC, i.receipt_no ASC)

Which I wouldn't necessarily think of as being more efficient. In fact if I run the two queries side by side on my larger data set Query 1. completes in a matter of milliseconds where as Query 2. takes several seconds.

However if I then add them as subqueries within a much larger query, the larger query completes in hours using Query 1. and completes in 40 seconds using Query 2.

I can only attribute this to the use of aggregate functions in one and not the other.

share|improve this question
1  
What is the database type and version? Also, have you looked into RANK() or equivalent? –  ANeves Nov 8 '12 at 14:14
    
The Database I'm working with is Visual dataflex 14.0 with a Sql server 2008 R2 back end. However any Sql commands I use would have to be backwardly compatible to atleast Sql server 2005. Preferably sql server 2000 if possible. –  DMK Nov 8 '12 at 15:22
    
I've never used RANK() before but I can definitely see myself using it again. Very useful thank you. I've added my rewritten Query using Rank() above. –  DMK Nov 8 '12 at 15:58
1  
FYI, RANK() is not available in SQL Server 2k. :( –  ANeves Nov 12 '12 at 8:27
add comment

3 Answers

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:

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.

share|improve this answer
1  
Completely agree with Alexander ...also tryin to add few index to speed a bit more –  paritosh Nov 9 '12 at 2:17
    
2 people completely agree, but this answer has neither up-votes nor edits to improve whatever could be better explained? I'm baffled. –  ANeves Nov 12 '12 at 8:30
    
The statement I made with regards to my programming language not supporting temporary tables is incorrect and I have marked it as such. I'm not sure where I drew this conclusion from? Therefore using a temporary table is a valid option. From what I can tell there are no differences in collation between columns included in the joins. Though the information you posted on collation within Sql made for interesting reading most of which I was unaware of. –  DMK Nov 12 '12 at 11:51
add comment

Setting the Date_Correlation_Optimization option to true within database>properties>options may also improved the overall speed without the need to rewrite the sub query.

share|improve this answer
add comment
up vote 0 down vote accepted

Following ANeve's comment -

I've also re-written the query using the Rank() command as suggested.

Query 3.

left join 
    (select 
        a.Person, 
        a.amount,
        (rank () over (Partition by a.Person order by a.payment_date desc, a.receipt_no desc)) 'Ranked' 
    from 
        Payment a
    Where 
        (a.reversed not in ('R') or a.reversed is null) 
        and a.type != 'GX'
    ) [lastPayment]  
on 
    [lastPayment].Person = [Person].Person 
    and [lastPayment].ranked = 1

This method has also resulted in the speeding up the larger query, the larger query now taking some 28 seconds

However Rank() is only supprted from SQL 2005 upwards.

share|improve this answer
    
I've selected this one as the answer as this is the answer I would have ideally implemented if it wasn't for the fact that I have to keep in mind backward compatibility with SQL 2000. –  DMK Nov 16 '12 at 9:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.