I have a very large table with over 200 million rows. I also have a huge SSIS package that brings in data from a staging table, usually in 20million record clips.
Basically, I have all the conditions written where if a record comes in and the customer exists (by using a phone number as a unique record), I send it either down an insert (SQL server dest.) or update path (OLE DB Command). I want to have the newest data, so each record has an OptInDate column data stamp. if there is a match on phone AND the optindate is newer, then I want to update...if it isn't newer, then do nothing. IF the phone doesn't have a match, then it does an insert.
I have all of this working great (logically speaking). Well, I hit a batch of data (about 60 million rows) that has much newer opt in dates, but it is customers we already have in the DB....so it will be an update condition.
Well, I was doing about 20million records processing per hour (which were only inserts). Well, as soon as started doing an update...it went to a crawl...like 100,000 records per hour.
update is a simple update customer set col_a= ?, col_b? where ID = ?
Everything is indexed I can seek records very fast.
I have tried doing a delete where ID? and then directing it over to the insert side again...but really, I had the same performance.
Typically, if I get 20million records in, about 25% of that will be new data (inserts) the rest will be updates.
I have thought about using the merge join to process the updates (which i will store in a staging table). How well will that work with something this big?
Thanks for your help and I hope I explained it well..
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Well, I bring all the CSV and scrub it all before it hits this work flow. I have another set of packages that homogenizes the data from the CSV (puts them in the correct columns). So in my big SSIS package, I am coming from a clean(er) SQL Server staging table.
All the cleanup/data conversion/etc happens in the same dataflow, but its before and writes to the DB occur. This seems to process fast for me...or fast enough for me....
Are you confusing a merge update with a merge join?
I've never heard of a merge update although I can never spend as much time as I would like learning new stuff.
You'd probably find it quickest to hive off your update data to a seperate table rather than updating one a t a time. Then at the conclusion of the data transfer, run a single T-SQL update statement to update based on your identified data.
>>You'd probably find it quickest to hive off your update data to a seperate table rather than updating one a t a time. Then at the conclusion of the data transfer, run a single T-SQL update statement to update based on your identified data.<<
That is what I was trying to say. I am glad you were able to communicate the idea more succinclty than I was able.
Business Accounts
Answer for Membership
by: acperkinsPosted on 2009-08-23 at 10:34:35ID: 25163465
I suspect the problem (correct me if I am wrong) is that you are committing the cardinal sin of importing and processing data directly into your Production tables. What you should be doing (if you are not alredy doing it) is to import into staging tables first and then you can validate and INSERT/UPDATE/DELETE from those staging tables.
Let me know if I am missing anything.