asked
SSIS Optimazation
I had an Backup server where I had two databases I had created an sp which gets data from Database 1 I had created an ssis which uses Oledb source which uses the sp to get data from Database 1 and then transfer the data to Database 2 in a Table named Master using Oledb destination component when I execute the sp in sql it takes only 9 seconds to get 436000 records (After clearing cache using DBCC FREEPROCCACHE) but when I runthe ssis to get the records and save it in table in another database it takes around 3 minutes or more Can anybody please suggest a way on how we can optimize this or do it other way
Based on the information provided, it looks as if the two databases are on the same server. The stored proc is only getting the data based on what has been provided. If you look in the execution log, you can get timings of each of the components. Some thing you may want to try on your destination is to change the rows per batch and check the "Table Lock" checkbox. Oh, and make sure SQL Server has an appropriate number set for max memory. You need to leave plenty of memory for the OS and for SSIS to process your records (takes plenty of memory to bring that data in from the source before sending it to the destination). All of that said, if the process is ever going to mean the databases are on different servers then leaving it as an SSIS package is fine. Since the two databases are on the same server, you might just write the stored procedure to do both the extract and insert. That is far more efficient than using ssis.
16 People are following this question.
