3

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
  • After doing nothing (computer probably rebooted overnight) the query now take 4s on the RDS which is way faster than my laptop! Really strange – Dan Jul 16 '15 at 14:04
1

You are using a scalar function in your WHERE clause, which means that function needs to be called multiple times. You should consider calling that function once (since you already know the values of @N and @Ticker) at the start of the script to return the scalar and then pass that scalar in as a variable. While not perfect, this should reduce the multiple calls that is likely slowing down your query. A low-end RDS box is sharing resources and it's quite possible that you are spinning more CPU ticks than necessary.

DISCLAIMER: I WORK FOR SOLARWINDS

We have a product (Solarwinds Database Performance Analyzer) that can monitor your RDS instance and help troubleshoot performance issues such as this.

  • Thanks, I can't calculate the value once as it changes for every record in the DB. It's not based on the date now but on the date field per record. I just don't see why it would make the RDS that much slower than my laptop. I'll double check the specs though and I'll look up solar winds tomorrow – Dan Jul 15 '15 at 20:43
  • If it helps, we have an AMI in the Amazon Marketplace. My guess about RDS running slower is simply compute time and resources. If you take the functions out of the code and made it inline SQL, you should see a boost in both. You can see a nice explanation here: sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/… – SQLRockstar Jul 16 '15 at 10:21
  • What is an AMI? If you could describe how I could use one of your products to debug this situation, that would be helpful... – Dan Jul 16 '15 at 10:23
  • AMI is Amazon Machine Image. I'd still suggest you try to change your code a bit first. As for our tool, it focuses on gathering details about the queries that are running and what they are waiting for. – SQLRockstar Jul 16 '15 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.