CODEWITHSUNDEEP
sqlsql-serverselectstored-procedures
slashNburn
slashNburn

Reputation: 474

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.

Upvotes: 1

Views: 209

Answers (2)

RizkiDPrast
RizkiDPrast

Reputation: 1725

Have you try something like this?

PROCEDURE [dbo].[GetRecentPrice] @currency nvarchar(6)
SELECT TOP 1 p.Price from PriceData p 
JOIN CurrencyPair c ON p.fk_CurrencyPair = c.Id
WHERE c.Name = @currency
ORDER BY p.Id DESC;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 2

Related Questions