slashNburn slashNburn - 1 month ago 5
SQL Question

SQL Server SELECT to timely return the most recent record

I have the below ‘PriceData’ database table that contains roughly 1,000,000 rows and is growing by about 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 I must be able to retrieve the most recent price for a given currency input, the below stored procedure achieves this but takes far too long to execute (3.4 seconds):

PROCEDURE [dbo].[GetRecentPrice] @currency nvarchar(6)
SELECT Price from PriceData
WHERE Id = (SELECT MAX(Id) FROM PriceData
WHERE fk_CurrencyPair = (SELECT Id FROM CurrencyPair WHERE Name = @currency));


SQL Server Execution Times: CPU time = 78 ms, elapsed time = 3428 ms.

I’ve tried including the below where clause that only examines the past minute of data:

AND TradeDate >= (SELECT (DATEADD(MINUTE, -1, (SELECT CONVERT(datetime, SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time'))))


But it’s only brought the execution time down by about 700 milliseconds:

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

I’ve also considered creating a new table that only stores the most recent price for each currency and is simply updated whenever a new price comes into the PriceData table. However that feels like a dirty hack and I’m sure violates some database normalization principles.

This stored procedure is executed by a web service layer which is consumed by an MVC application so the execution time needs to be much better (I'd like to get it to < 100 milliseconds). I’m open to modifying the architecture of this table and database.

Answer

You can try using TOP 1 with a JOIN and see how it compares to your original query:

PROCEDURE [dbo].[GetRecentPrice] @currency nvarchar(6)
SELECT TOP 1 t1.Price
FROM PriceData AS t1
JOIN CurrencyPair AS t2 ON t1.Id = t2.Id
WHERE t2.Name = @currency
ORDER BY t1.Id DESC

You should also place an index on Id field of both PriceData and CurrencyPair tables.