Background - I built a tool which grabs a lot of data from multiple third party APIs and stores it in a MSSQL DB. Then, I built a visualization to display this data in tabular and chart format.
Problem - I just recently migrated from MSSQL 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+MSSQL 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!!