2
\$\begingroup\$

I have two tables (parent-child model): Loans & Transactions.

Each Loan row has multiple Transaction rows.

I want to select all rows from Loans together with most recent row from Transactions.

Here is my select to achieve this.

SELECT 
    L.*, T.*
FROM Loans L
CROSS APPLY
(
    SELECT TOP 1 * FROM Transactions AS T
    WHERE L.LoanID = T.LoanID
    ORDER BY CRTime DESC
) T

Another approach

SELECT 
    L.*, T.*
FROM Loans L
INNER JOIN
(
    SELECT MAX(TransactionID) AS TransactionID, LoanID FROM Transactions AS T
    GROUP BY LoanID
) T
ON T.LoanID = L.LoanID
INNER JOIN Transactions TS
ON TS.TransactionID = T.TransactionID

Any ideas on improving it?

Side question: Can you suggest best practices for comparing two queries running times?

\$\endgroup\$
2
\$\begingroup\$

Select *

Generally, it is advised against using SELECT *. The answers to this stackoverflow question explain why SELECT * is considered harmful.

Your queries might return different values

The first query takes the transaction record with the latest CRTime, the second uses the highest ID. These are likely returning the same rows right now, but I would use the CRTime. The rows might not have been inserted in time order, so the most recent time and the highest ID might not result in the same record.

I would recommend the first query

I would recommend the first query because it avoids that second join that the second query has. Other than changing the SELECT *s to list all the columns and making sure the tables are properly indexed, I don't think there is much you can do to improve it. That's a pretty nice query.

Select * and Indexing

One of the problems pointed out in the linked SO question is that fact that using SELECT * can cause the query to not use indexing properly as it brings back more columns than you need.

Checking runtimes

I found this stackoverflow question had some good answers regarding comparing SQL runtimes/performance.

\$\endgroup\$
  • \$\begingroup\$ @PenutReeaper Thanks for response. T.* and L.* war just for the purpose of making the query shorter for codereview site. Anyways, it's gonna help others! \$\endgroup\$ – levi Feb 26 '15 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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