Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
It's taking so much time to sync over a network RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I have a windows service for syncing tables over a network. I am doing some table upload, table download and a table upload & download.

    The thing is when I checked the log, it shows to sync around 10,000 records it is taking more than 12 hours. And this is only for Upload & Download. Do you have any idea why this is taking so much time. 

    Appreciate any help.

    Happy Coding.

    Regards,

    Jaliya

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Tuesday, May 21, 2013 5:26 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    you can enable sync framework tracing so you can see where it's spending most of its time.

    just a quick check though, does the source and destination have the same pre-existing data on them?

    Tuesday, May 21, 2013 11:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    i can't open your zip file. can you just lookup the trace if there's anything relating to error or conflict?

    your trace above also looks like its not using the bulk_ stored procedures. 

    Thursday, May 23, 2013 12:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    the bulk procedures are only supported on SQL 2008 and above.
    Monday, May 27, 2013 5:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    not for 2005. it's only for 2008 and above. yes, it will work with 2008 express.
    Monday, May 27, 2013 5:35 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    you can enable sync framework tracing so you can see where it's spending most of its time.

    just a quick check though, does the source and destination have the same pre-existing data on them?

    Tuesday, May 21, 2013 11:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear JuneT,

    Nice to see your reply. I have enabled tracing and sync is taking like 1 second to insert metadata for a particular row. The thing that I cant understand is, this is only happening when I am doing a Upload and Download. Yes, I do have pre data on both local and remote. But it is comparatively small amount of rows like 13,000.

    Another thing, one of my tables I have only 230 records. But when sync is completed in sync stats it shows more than 10,000 changes applied. How is this possible when I only have 230 records.

    Waiting till your reply.

    Thanks,

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com


    Tuesday, May 21, 2013 12:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    can you post the trace?

    Tuesday, May 21, 2013 1:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear June,

    the log file was very large and currently I don't have access to the logs. I can send the trace in another 13 hours.

    It was like,

    Executing Command: [EMPLOYEE_insert]
    Parameter: @id: 1
    Parameter: @FirstName: Jaliya
    Parameter: @LastName: Udagedara etc

    for all the records. The upload sync and the download sync is working as expected. Only the Upload & Download sync is slow. I am using a Filter, but I don't think it's the reason behind this.

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Tuesday, May 21, 2013 2:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear June,

    I have uploaded the sync to my skydrive. Can you please check it.

    Sync Trace

    Thanks.

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Wednesday, May 22, 2013 4:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I am still facing this issue and can't find a single cause. I am syncing the same table with Head Office SQL Server database and several remote databases. Some syncs works in a good speed. Some are not. I don't think it is a network issue, as we are in a VPN.

    Appreciate your input.

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com


    Thursday, May 23, 2013 4:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    i can't open your zip file. can you just lookup the trace if there's anything relating to error or conflict?

    your trace above also looks like its not using the bulk_ stored procedures. 

    Thursday, May 23, 2013 12:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I am sorry that you couldn't open the file. There wasn't any conflicts. But yes, sync is not using bulk commands.

    I checked the db trace at the remote locations using the profiler. It shows tablename_insert followed by the tablename_insertmetadata for every record. Its inserting record one by one.

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Thursday, May 23, 2013 1:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    but you have the bulk procedures created?

    Thursday, May 23, 2013 2:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I didn't create any additional procedures except for the ones that was created by sync. I am calling the sync method of the orchestrator.

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Thursday, May 23, 2013 4:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    i mean did the provisioning create the bulk procedures? do you see any stored procedure with the word "bulk"? what version of SQL Server are you running this?

    also can you profile the selectchanges sp and see if it actually has the filter you specified?

    Friday, May 24, 2013 1:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear June,

    I am sorry for the late reply. Just now came to the office and checked the stored procedures. The SQL Server version is 2005 Express SP3. Provisioning has not created any bulk procedures. What can be the reason? Is it because of the SQL Server version? I think that can be the issue right? 

    Awaiting your kind response.

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Monday, May 27, 2013 3:54 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    the bulk procedures are only supported on SQL 2008 and above.
    Monday, May 27, 2013 5:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    by setting SetUseBulkProceduresDefault(True), will it create bulk procedures on SQL Server 2005 express?

    Will bulk procedures get created on SQL Server 2008 Express?

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Monday, May 27, 2013 5:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    not for 2005. it's only for 2008 and above. yes, it will work with 2008 express.
    Monday, May 27, 2013 5:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    oh!!!

    do you think it can be the reason behind syncs' slow down? I have a requirement where I want to push remote locations large amount of data (around 1 million). If I didnt upgrade SQL 2005 Express editions to SQL 2008 Express, I may never be able to push those data. Is there any workaround for SQL 2005 Express to use bulk procedures?

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Monday, May 27, 2013 5:54 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    if you don't have bulk procedures, and let's say you have 1000 rows, you'll be sending 1000 insert statements to your SQL service.
    Monday, May 27, 2013 6:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you so much JuneT. Yes, I can understand, it will definitely increase the network traffic too. If I manually create the bulk procedures on SQL 2005 Express, will they be used when syncing?

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Monday, May 27, 2013 6:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear June,

    I just checked the sync with two SQL 2005 express editions. One has already pre loaded data and other was empty. For the empty database it got completed syncing 20000 records taking around 2 minutes even without having bulk procedures. The pre loaded database was slow and still syncing, and it has some conflicts. 

    Jaliya Udagedara(MCPD,MCSD) http://www.jaliyaudagedara.blogspot.com

    Monday, May 27, 2013 6:29 AM