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
Martin Smith :
As you only have 26 symbols and a million rows 26 index seeks may be better (assumes an index on Symbol ASC, Id DESC)\n\nDECLARE @Id INT,\n @Symbol VARCHAR(10)\n\nDECLARE @Results TABLE(\n Id INT,\n Symbol VARCHAR(10))\n\nSELECT TOP 1 @Id = Id,\n @Symbol = Symbol\nFROM Forex\nORDER BY Symbol ASC,\n Id DESC\n\nWHILE @@ROWCOUNT = 1\n BEGIN\n INSERT INTO @Results\n VALUES (@Id,\n @Symbol)\n\n SELECT TOP 1 @Id = Id,\n @Symbol = Symbol\n FROM Forex\n WHERE Symbol > @Symbol\n ORDER BY Symbol ASC,\n Id DESC\n END\n\nSELECT *\nFROM @Results \n",
2013-07-09T13:17:23