May 28, 2002 at 8:32 am
How can I improve the time it takes to load the data from one server (dB) to another dB server on the same network?
Christine
Aurora
May 28, 2002 at 8:49 am
Depends on the amount of data and exactly what you want to do.
You could if the databases are the same and you need to copy the data make a file backup on the first server, zip it up (usually about 18% it's original size), send over, unzip and restore to the other server.
Or if you are using Export/Import wizard drop indexes, constraints and keys and insert the data, then add all back after.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 28, 2002 at 9:24 am
Well I am moving data from a staging dB to a datawarehouse. (Unfortunately, no straight copy). The thing is I am moving less than 2GB [initially, eventually will be even less] of data. And I am not transforming any data types. Just have a few business rules in place [hence I can't drop check constraints, I don't think]. Our method currently, is to move this data via stored procedures that are written to 'push' the data from staging to the dw. Any advice???
Christine
Aurora
May 28, 2002 at 11:53 am
Well, I can offer lots of advice but what do you have to work with as far as network an server hardware and current setup on DW. One thing you could do is create a local staging table on the DW server and copy the data in it without any indexes constraints (etc.). Then you move the data to the main table. This should allow the data to cross the network faster. Or do the same but bcp out the data, zip it, move to DW server, unzip, bcp in to staging table then move to main table. There are lots of possibilities.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 29, 2002 at 8:30 am
You have been so helpful, thanks. I am working with brand new servers, at least 40GB hard drives, 1GB of RAM, 512 Cache, Dual Pentium III 1GHz processors. I think there're pretty beefy for our relatively small dB's. Now the network is another issue. I have a theory but am unable to substantiate it. I feel the network is playing a role here, along with the indices that are being rebuilt. I am unable to "prove" that the network is slow (regarding the data movement). Is there a way I can test that theory from my end? Finally, we used stored procedures that check for certain criteria to "determine" what data should move. The sprocs are pretty standard (nothing complex) and are written according to best practice(industry). Any other thoughts given the above info, again it is much appreciated as I have to decide what will be the best/most efficient and most time saving way to move this data?
Thanks...
Aurora
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply