Sql-server – AWS RDS SQL Server extremely slow compared to EC2 with SQL Server

sql serverstored-procedures

Background: I built a tool which grabs a lot of data from multiple third party APIs and stores it in a SQL Server database. Then, I built a visualization to display this data in tabular and chart format.

Problem: I just recently migrated from SQL Server on EC2 (m1.medium) to RDS (db.m3.medium) and used this tool to get all my data into my RDS instance.

My application layer is PHP on EC2. Everything is working perfectly. After migration I was able to enter ~300,000 rows INTO the DB via PHP and ODBC driver. Today I went to run a transform stored procedure which will take the ~300,000 rows of data from landing and move it into dbo. On EC2+SQL Server this script took at most 15 minutes. Now using RDS this script has taken up to 5 hours.

My transform script simply creates temp tables, moves the data from landing into the temp tables, creates a join between temp table and dbo, and where there is no data in that join inserts the data from temp table into dbo. In short, if the data is not in dbo it inserts it.

Example logic is included below:

SELECT DISTINCT * 
INTO ##temptable 
FROM lnd.landingtable

INSERT INTO dbo.realtable(
    [DataTable].id
    ,[DataTable].name
    ,[DataTable].createdBy
    ,[DataTable].createdDate
    ,[DataTable].updatedBy
    ,[DataTable].updatedDate)
SELECT 
    [Landing].id
    ,[Landing].name
    ,SYSTEM_USER
    ,GETDATE()
    ,SYSTEM_USER
    ,GETDATE()
FROM 
    ##temptable [Landing]
    LEFT JOIN dbo.realtable [DataTable] ON [Landing].id = [DataTable].id
WHERE 
    [DataTable].id IS NULL

I cannot figure out what I am doing wrong. I did not provision IOPS due to cost however from what I read online this should not be necessary. The difference in performance is so dramatic that I cannot understand why anyone would use RDS. Obviously I am doing something majorly wrong. Any guidelines, links, or comments would be greatly appreciated!!

Best Answer

If you want to remove them indexes run this script on each database you restore

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'DROP INDEX ' 
  + QUOTENAME(name) + ' ON '
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ';
'
FROM sys.indexes 
WHERE index_id > 0
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
and name like '%ci_az%'
ORDER BY [object_id], index_id DESC;

EXEC sp_executesql @sql;
Related Question