SQL Server - Optimize this query with million records

Question

I have table with 2 million records for now, it will increase by 0.05 million records per day, so I want optimize this query

Select * from Forex where Id in 
(SELECT MAX(Id) FROM Forex GROUP BY Symbol having Symbol in 
(Select Distinct Symbol from Forex) )

I have create NONCLUSTERED INDEX so the time taken for this query is 673milliseconds. I have modified the query to

Select * from Forex where Id in 
(SELECT MAX(Id) FROM Forex GROUP BY Symbol having  Symbol in 
('AUDCAD','AUDCHF','AUDJPY','AUDNZD','AUDUSD','CADCHF','CHFJPY', 
'EURAUD','EURCAD','EURCHF','EURGBP','EURJPY','EURNOK','EURNZD', 
'EURUSD','GBPCAD','GBPCHF','GBPJPY','GBPUSD','NZDJPY','NZDUSD', 
'USDCAD','USDCHF','USDJPY','USDNOK','USDSEK'))

Now the time taken is 391milliseconds

Is it possible to make less than 100milliseconds? Or Some one help to optimize this query

Answer 1

As you only have 26 symbols and a million rows 26 index seeks may be better (assumes an index on Symbol ASC, Id DESC)

DECLARE @Id     INT,
        @Symbol VARCHAR(10)

DECLARE @Results TABLE(
  Id     INT,
  Symbol VARCHAR(10))

SELECT TOP 1 @Id = Id,
             @Symbol = Symbol
FROM   Forex
ORDER  BY Symbol ASC,
          Id DESC

WHILE @@ROWCOUNT = 1
  BEGIN
      INSERT INTO @Results
      VALUES      (@Id,
                   @Symbol)

      SELECT TOP 1 @Id = Id,
                   @Symbol = Symbol
      FROM   Forex
      WHERE  Symbol > @Symbol
      ORDER  BY Symbol ASC,
                Id DESC
  END

SELECT *
FROM   @Results