December 19, 2007 at 7:56 am
Yes, you did read the title correctly. @=)
I have an SSIS package on my box from which I do magic between a SQL 2000 & 2k5 instance. As an after thought, I was asked to save certain columns from the 2000 instance for a month so we had a "backup" of data to call from.
The database I'm pulling the data from is 150 GB and we don't have the space to keep a restored copy of the entire database. Especially since I only need about 17 tables and between 3-19 columns from each table (depends on the table). The problem is that the tables have millions of records in them, so even copying 3 or 4 or 5 million records with a few columns is taking FOREVER.
In a sequence container, I truncate the destination tables with an Execute SQL Task. The destination DB is set to SIMPLE recovery and is on the same SQL 2000 instance as the source. We have plenty of hard drive space for this small amount of data copying. And I have a DataFlow task with multiple OLE DB sources & destinations, one pair for every table I need to "copy".
Seems simple enough, but I'm wondering if I'm missing something that could make this process go faster. I've been executing this single DataFlow task for 4 hours now and only 3 tables have completely copied over. 5 are still in process and 9 tables haven't even started copying yet.
For the destination tables, I do have "Table or view - fast load" chosen. All the defaults have been left alone. I'm thinking of removing the "Check Constraints" click since I don't have any constraints on the destination tables, but am unsure if this will really assist performance.
Does anyone have any thoughts on how I can improve performance in this task or is this a case of "You just have to wait until it's done"?
December 19, 2007 at 8:34 am
Did you set the Maximum Insert Commit Size on each destination? IF left to 0 it does one BIG transaction so committing after x rows will probably free resources and speed it up. I tried this with a single integer column million row transfer and it did not help on that, but with a wider transfer and more rows it may help.
How are you filtering the columns you are transferring? If you are filtering in the Source by de-selecting the columns you do not want then you are pulling ALL the columns from the server into menu and then filtering which will be slower than using a SQLCommand and using a select statement with only the columns you need. Basically going from Select * to Select cols.
Are you using table lock on the destination? I tested a single integer column million row transfer from one 2005 table to another and using the table lock option took it from 23 seconds to 7 seconds. Of course you may not have this option.
My local user group met last night and SSIS performance was the topic. Brian Knight was the speaker.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 19, 2007 at 8:44 am
I am filtering in the source by Deselecting all the columns. DIdn't think about the effect that would have... I should probably change that to a query instead.
I did just research the Batch size and the Maximum Insert Commit Size features. I think I'll set those at 10000 and see what happens with this.
I am using Table Lock on the destination. It was one of the defaults.
Brian Knight? I know him. Used to work with him. Are you in the Orlando SQL Server group or someplace else? I'm trying to hook up with my local SS group but can't seem to find them anywhere. And I'm pretty sure one exists...
December 19, 2007 at 8:51 am
If I had not gone to the meeting last night I would have done the same thing for the columns. I had always assumed that it was building the select, not filtering AFTER selecting.
Yes I am in the Orlando SQL Server Group. I just moved here and last night was my first meeting. This group was easy to find. Andy Warren does a great job getting it out there.
Anyway, hope you can get it figured out. I am now tapped out for ideas.
Let me know what happens.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 19, 2007 at 10:45 am
Well, I updated my Batch size and Max Commit to 10000 and set my sources as queries. Started the run about 1 & 1/2 hours ago and already I have 9 tables completely loaded.
WHAT A DIFFERENCE! WOW!
Thanks for the suggestion, Jack. I don't know why I thought the queries as a source would take much longer than a straight table load.
BTW, welcome to Florida (if you didn't live in another FL city previous to this). I live a few hours north in Jacksonville. I might have to start going to some of the Orlando meetings if I can't find the Jax group soon.
December 19, 2007 at 11:11 am
Thanks for the welcome to FL. I am really enjoying the weather.
I know there is something going on as far as a Jacksonsville PASS group. There is a SQLSaturday being planned for there in the April/May time frame. You may want to fire an email to Brian as he would probably know when and where the group is meeting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply