Post reply

Questions about bulk data movement

  • October 14, 2009 at 12:22 pm

    #212179

    Hi Experts,

    I have a very unique requirement and I need some advice on the best strategy to tackle it. I have 5 tables with a combined data of about 538 million rows (40GB). out of those 5 tables, 1 of them (5GB) is actively queried. I need to consolidate all 5 tables into one table that would be queried from time to time. Here is what I think should be done i.e. my ideas

    -Consolidate the other 4 (non queried table) first into one table

    -Move the 5th active table data to this new table

    Are there better ways to achieve this? What about locking on the actively queried table when moving data? Would I really need to partition (date range) the 22GB data or indexes would do just fine for querying? Whats is the best way to actually move the data? BCP? SSIS?

    Any suggestions from you experts would be greatly appreciated. Thanks

  • October 15, 2009 at 4:37 am

    #1066165

    The data in the individual tables are already of high volume and combining those into a single table would again hit performance. It does require partitioning of the data. Use SSIS with fast-load option with other performance checks.

  • October 15, 2009 at 10:16 am

    #1066378

    Thanks for your input, it has been duly noted !

  • October 16, 2009 at 3:23 am

    #1066764

    Can u give us the details why you need to do this. It may help us to give you better answers.

    Edit - How about using views.

    "Keep Trying"

  • October 16, 2009 at 1:53 pm

    #1067173

    For reporting purposes, we are still working towards a data warehouse so at the moment we rely on the oltp for reporting.

  • October 16, 2009 at 2:17 pm

    #1067191

    This sounds to me like you need to do this one time for all of the data and then develop a process to keep the 'reporting' table up to date. Why not build out the bulk of your 'reporting' table from a backup of your production tables. Then you just need to worry about creating a process that keeps the 'reporting' table up to date.

    How real-time does your reporting need to be? Why not just report off of a day-old copy of your DB?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply