SQL Server SELECT to timely return the most recent record -

i have below ‘pricedata’ database table contains 1,000,000 rows , growing 100,000 records/day:

id   | datecreated    |tradedate        |fk_currency | price -------------------------------------------------------------- 48982| yyyymmddhhmmss | yyyymmddhhmmss  |1           | 1.09684 48953| yyyymmddhhmmss | yyyymmddhhmmss  |1           | 1.22333 48954| yyyymmddhhmmss | yyyymmddhhmmss  |2           | 1.22333 

my requirements mandate must able retrieve recent price given currency input, below stored procedure achieves takes far long execute (3.4 seconds):

procedure [dbo].[getrecentprice] @currency nvarchar(6) select price pricedata  id = (select max(id) pricedata      fk_currencypair = (select id currencypair name = @currency)); 

sql server execution times: cpu time = 78 ms, elapsed time = 3428 ms.

i’ve tried including below clause examines past minute of data:

and tradedate >= (select (dateadd(minute, -1,  (select convert(datetime, sysdatetimeoffset() @ time zone 'central standard time')))) 

but it’s brought execution time down 700 milliseconds:

cpu time = 62 ms, elapsed time = 2762 ms.

i’ve considered creating new table stores recent price each currency , updated whenever new price comes pricedata table. feels dirty hack , i’m sure violates database normalization principles.

this stored procedure executed web service layer consumed mvc application execution time needs better (i'd < 100 milliseconds). i’m open modifying architecture of table , database.

you can try using top 1 join , see how compares original query:

procedure [dbo].[getrecentprice] @currency nvarchar(6) select top 1 t1.price pricedata t1 join currencypair t2 on t1.id = t2.id t2.name = @currency order t1.id desc 

you should place index on id field of both pricedata , currencypair tables.