January 3, 2008 at 6:39 am
I have a very small number of records (< 100) that I am trying to pull from a database on a SQL 2000 server to one in SQL 2005. The source is constantly changing, so I will be running my package as close to continuously as possible, to ensure I have the most current data.
The package, in both SSIS and DTS consists of the following:
1) A task to populate a global variable from the max(ID) in the destination table
2) A data pump using SQLOLEDB source and destination, calling a sproc to pull the records with the global variable as a parameter for the sproc.
In DTS, the package completes in sub-second time.
In SSIS, the package takes over 1.5 minutes.
When watching the SSIS package execute, the Pre-Execute and Execute phase of the data-flow task each take 30+ seconds.
I've researched and tried nearly every performance enhancing configuration option that I've found for SSIS.
Please help!
January 4, 2008 at 11:54 am
During the pre-execute phase, objects cache information that they can. So, if you just have an OLEDB Source and a OLEDB Destination in your package the pre-execute is probably pulling the data from your procedure into the buffer.
There are a lot of performance tuning options within the SSIS components, so it may be something you have missed. Start with turning off the options for checking against external schema.
If you are using an "upgraded" package that was created by a SQL 2005 upgrade, I would suggest re-writing it from scratch before going any further troubleshooting it.
On a more important note, what you are doing is probably not a good idea. If you are trying to keep identical tables synchronized, transactional replication is a much better option. If the tables are not identical, SQL 2005 gives you some additional options for "seeing" changes real-time and handling them in a much more event-driven manner. 25 years ago developers moved to event-driven programming, it's about time database developers did the same.
If I seem to need to check a table for changes more than once every 20 minutes, I start moving to something event-driven rather than a constantly looping process.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply