views:

271

answers:

1

trying to remove the nested loop in the execution plan of a query i have (mssql 2005). have the following table:

TxnID bigint CustID bigint col1 varchar(4) col2 varchar(4) col3 varchar(4) TxnCurrency char(3) TxnAmt money TxnDate datetime

-- query 1
SELECT CustID, TxnCurrency, SUM(TxnAmt) AS TxnAmt
FROM   table
WHERE  TxnDate >= @date1 and TxnDate < @date2
       and col1 IN ( @list )
       and col2 = @param
GROUP BY CustID, TxnCurrency

-- query 2
SELECT TxnCurrency, SUM(TxnAmt) AS TxnAmt
FROM   table
WHERE  TxnDate >= @date1 and TxnDate < @date2
       and CustID = @custID
GROUP BY TxnCurrency

TxnID is the Primary Key, have non clustered index on CustID, TxnDate

should i create another index with include columns to resolve the nested loop?

A: 

Are these queries separate or are they being joined?

In query 1, if you're going to be selecting based on TxnDate alone, you should probably put a non-clustered index on that column.

Nested loops aren't always a bad thing. Depending on the size of the sets you're working with, they can be optimal. Do you think you'd see better results with Merge or Hash join?

Bob Pusateri