Adventures in SQL Server Performance Tuning – Joins vs. Where Exists

What is wrong with this query?

SELECT c.CustomerName, b.BillingID
FROM BillingInfo bi
JOIN Customer c
ON bi.CustomerID = c.CustomerID
JOIN DailyTransactions dt
ON bi.BillingID = dt.BillingID
WHERE dt.Transdate >= @startdate
AND dt.Transdate < @enddate

Well, the first thing that I see is that we are joining to the DailyTransactions table, but we aren’t returning
any columns in the results set. Why? The reason is because the goal of this query is to return all customers
that have transactions in a given period, so we must check the DailyTransations table for this. But we don’t care
if the customer had 1 transaction or 1000, nor do we need any columns of data from that table, so here is better
(quicker!) way to do this:

SELECT c.CustomerName, b.BillingID
FROM BillingInfo bi
JOIN Customer c
ON bi.CustomerID = c.CustomerID
WHERE EXISTS (SELECT 1 FROM DailyTransactions dt
WHERE bi.BillingID = dt.BillingID
AND Transdate >= @startdate
AND Transdate < @enddate)

A WHERE EXISTS will be much faster than a join because as soon as the first match is found for that BillingID,
SQL Server stops looking for more, unlike a join, which will match up every row.

When I fixed a stored procedure with this exact problem, I cut the run time required by two-thirds. Since this stored
procedure loads a dropdown in Reporting Services, that cut the time required to load the report.