I am testing out AWS RDS to replace a local DB and in general I am finding the latency etc acceptable for most of our use-cases. There is one particular query that we run manually (i.e. in SQL Server Management Studio) from time to time that takes a while to execute. With the DB as it stands, that query is taking just under 2 minutes to run on my local machine. However, I left the same query running on an identical DB on RDS running for well over 4 hours and it still had not completed! (it actually runs so long that I have no idea if it ever completes).
The query itself only returns 23 records, 5 columns each. This is a tiny amount of data that needs to be sent back so I can't see this being a network latency issue. I am still testing this out on the free RDS (i.e. lowest spec) but I'm hardly running it on a supercomputer locally so I could understand it taking 10, even 50 times longer but over 4 hours doesn't seem right just because the server is the lowest spec offered.
Also note that the DB table being queried has the same index applied etc.
So I am hoping that there is something about my query maybe that AWS RDS is particularly bad at, or requires some config change first? The query does use a scalar-valued user defined function which uses TSQL TOP.
I'll post the query code here, I don't expect anyone to analyse it though, I was more hoping someone knows of a config setting on RDS or a particular feature of TSQL to avoid?
Query:
DECLARE @N as int = 1,
@Ticker as nvarchar(50) = 'USDZAR',
@StartDate as date = '2015-06-08'
SELECT DISTINCT M.TradeDate,
T.[Moneyness-],
T.[Volatility-],
T.[Moneyness+],
T.[Volatility+]
FROM YieldX_MTM as M
LEFT JOIN (SELECT C.TradeDate, C.Moneyness as [Moneyness-], C.Volatility as [Volatility-],
D.Moneyness as [Moneyness+], D.Volatility as [Volatility+]
FROM (SELECT TradeDate, MAX(Strike/Future) as Moneyness, MIN(Volatility) as Volatility
FROM YieldX_MTM
WHERE TradeDate > @StartDate
AND Ticker = @Ticker
AND Category = 'Foreign Exchange Future'
AND InstrumentType <> 'F'
AND Strike/Future <= 1
AND Expiry = dbo.GetRelativeExpiryCurrency(TradeDate, @N, @Ticker)
GROUP BY TradeDate) as C
JOIN (SELECT TradeDate, MIN(Strike/Future) as Moneyness, MIN(Volatility) as Volatility
FROM YieldX_MTM
WHERE TradeDate > @StartDate
AND Ticker = @Ticker
AND Category = 'Foreign Exchange Future'
AND InstrumentType <> 'F'
AND Strike/Future >= 1
AND Expiry = dbo.GetRelativeExpiryCurrency(TradeDate, @N, @Ticker)
GROUP BY TradeDate) as D
ON C.TradeDate = D.TradeDate) as T
ON M.TradeDate = T.TradeDate
WHERE M.TradeDate > @StartDate
AND Ticker = @Ticker
AND Category = 'Foreign Exchange Future'
AND InstrumentType <> 'F'
AND Expiry = dbo.GetRelativeExpiryCurrency(M.TradeDate, @N, @Ticker)
ORDER BY TradeDate
GetRelativeExpiryCurrency UDF:
CREATE FUNCTION [dbo].[GetRelativeExpiryCurrency]
(
@Date DATE,
@N INT,
@Ticker NVARCHAR(50)
)
RETURNS DATE
AS
BEGIN
-- Declare the return variable here
DECLARE @Expiry as DATE;
IF @N > 0
BEGIN
SELECT @Expiry = MAX(Expiry)
FROM (SELECT DISTINCT TOP(@N) Expiry
FROM YieldX_MTM
WHERE Expiry >= @Date
AND Ticker = @Ticker
AND Category = 'Foreign Exchange Future'
ORDER BY Expiry) as T
END
ELSE
BEGIN
SELECT @Expiry = MIN(Expiry)
FROM (SELECT DISTINCT TOP(-@N) Expiry
FROM YieldX_MTM
WHERE Expiry < @Date
AND Ticker = @Ticker
AND Category = 'Foreign Exchange Future'
ORDER BY Expiry DESC) as T
END
RETURN @Expiry
END
