Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!!

share|improve this question

migrated from serverfault.com Jul 5 at 6:41

This question came from our site for professional system and network administrators.

2 Answers 2

It seems that the tool I used to migrate my DB from EC2 to RDS added a bunch of weird indexes which caused the performance of the DB to tank. Once I removed any and all elements of the DB with 'azure' in their name my performance returned to normal! The tool was extremely useful but if anyone happens upon this question know that you have to remove anything Azure created other than your original data.

share|improve this answer
    
It is completely normal (and expected) practice to disable indices during substantive bulk loading, and rebuild them upon completion. I would check closely why these indices were created before deleting them permanently, or you are likely o be back here wondering why your application is now slow and/or corrupt. –  Pieter Geerkens Sep 14 at 16:34

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;
share|improve this answer
    
Thanks Thomas, I don't have the indices anymore to test this script but the logic is the same as my solution - remove the indices added by 'Azure'. –  gr4y Oct 17 at 13:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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