August 31, 2009 at 7:25 am
Hi,
This is my another post for updation. First of all i want to thank you all for giving me a a direction that Fastet way to update 10 million records. - http://www.sqlservercentral.com/Forums/Topic684049-145-1.aspx
I worked on the suggestion given by you all and it works fine for me and client.
Now i have another problem.
User wants to see all the selected/updated records in grids.
S/he also want a log file for each and every erroneous record.
Approach on which i am thinking..........
1. Select records using for XML or Auto XML
2. Do all the updation in this XML file.
3 At last update records in DB using this XML file.
Why i am not using ADO DataSet's update method because user wants to see all the "To be Updated and Actual Updated" records and a log file for erroneous records.
Is this XML apporach work fine?
Please comment and reply.
I' am waiting for your valuable suggestions.
Thanks ,
Mithilesh
MJ:cool:
August 31, 2009 at 1:09 pm
You're not providing nearly enough information for anyone to answer this cogently. That said - what tool do you plan on using to read 10 Million records you've dumped into a flat file?
Something that big should be done inside of the RDBMS, not out. You want something that can perform updates quickly and efficiently. Slow against a flat flie with 10M rows = you can go home for the week while this processes, IF it finishes.
I would consider dumping this to some separate table if the requirements demand it (i.e. you cannot keep track of the changes in some other way).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 1, 2009 at 5:44 am
Matt is right you would want to do this inside the database rather than outside. One suggestion is to do this update in small batches rather than on single update. Need more info to give a proper answer.
"Keep Trying"
September 2, 2009 at 3:52 pm
Hi,
I would stage the data in a staging table. Then you could load the data into grid in batches for review before moving to the traget table.
September 4, 2009 at 3:42 am
I am using VB.Net as frontend and SqlServer2005 as RDBMS.
It is client's requirement that s/he wants to see all the records before and after updating.
As Michale and chirag suggest either Batch update or use staging.
Another point I would like to mention here is that the scenario ( updating 10 million records) I am taking is worst scenario , but I definitely sure that s/he can update at least 50K -1 million records in single update.
MJ:cool:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply