I have a table which has around 700,000 rows. It has 11 columns, out of which one is an varchar(max)) column and it has xml data. Now I have to transfer this data from one server to another. I did the follwoing on the SSIS package. I wrote my query to take first 100,000 in a single data flow task and for every 100,000 i added one data flow task and I modified the defaultbuffersize to (10 MB - 100 MB) and defaultbufferRow as low and also high, But its taking long time to query the table but its writing the data so quickly. I tried by removing the varchar(max) column and its not even taking 5 mins.
So with the addition of varchar column it takes around 13 hrs.
After I tried with .Net code also by dataset, bulkinsert and used TPL to make use of my all processord. But i am not able to see any sinificant differences.
Any help would be appreciated.
700000 is not a lot of rows. Do you really need varchar(max) I.e. Does the len() ever exceed 8000? If not - cast it as varchar(8000) then is will be smaller buffer size. Could the network I/o be an issue? Are you moving data from us to Europe and back again for example. In which case increase your network packet size to 33k. On modern hardware this should not take more than 1 hour to transfer this data. I'd hope for 10 mins! A good test is how long to transfer to an empty table with no indexes or constraints. If this is quick then you should see where the prob lies.