none
Trans-Continental Stored Procedure Call Very Slow RRS feed

  • Question

  • Hello,

    I'm calling a stored procedure that takes a table-valued parameter with SqlCommand.ExecuteReader (.NET 4.0).  I'm passing a DataTable with about 1.3 million rows (about 100 megabytes).  For debugging, I added some statements to several points in my stored procedure that write some text that includes GETDATE() to a logging table, including at one point near the start of the stored procedure, before the BEGIN TRANSACTION.  ExecuteReader runs for about 57 minutes!  However, that first logging statement in the stored procedure happens about 90 seconds before ExecuteReader ends -- i.e., ExecuteReader takes 57 minutes but the stored procedure only executes for 90 seconds.

    What's happening (or how can I determine what's happening)?

    Thanks.


    Uncaged


    • Edited by Uncaged Wednesday, May 7, 2014 2:37 PM
    Monday, May 5, 2014 3:28 AM

Answers

  • It appears that much of the delay was in communicating with an SQL Server on the other side of the planet.  I tried running the application on an Azure VM in East Asia, and instead of 57 minutes, it took 3 (including the 90 seconds that the stored procedure actually ran).


    Uncaged

    • Marked as answer by Uncaged Wednesday, May 7, 2014 2:35 PM
    Monday, May 5, 2014 11:40 PM

All replies

  • Hello Uncaged,

    From my opinion, I guess that the DataTable with about 1.3 million rows may have effect for the executed time. As far as I know, when we query the database with parameters, it will upload these parameters data to the server side, since there are 1.3 million rows needed to be uploaded, it may cost a larger time for it.

    You can have a try to upload a smaller DataTable to check whether the ExecuteReader() method will cost fewer time than 57 minutes.

    If I misunderstand, please correct me.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 5, 2014 6:49 AM
    Moderator
  • A little more info: The application is running on a server on the East Coast of the U.S., and the SQL server is on an Azure server in Asia.


    Uncaged

    Monday, May 5, 2014 2:56 PM
  • It appears that much of the delay was in communicating with an SQL Server on the other side of the planet.  I tried running the application on an Azure VM in East Asia, and instead of 57 minutes, it took 3 (including the 90 seconds that the stored procedure actually ran).


    Uncaged

    • Marked as answer by Uncaged Wednesday, May 7, 2014 2:35 PM
    Monday, May 5, 2014 11:40 PM
  • Hello,

    >>It appears that much of the delay was in communicating with an SQL Server on the other side of the planet.

    Yes, since it needs to pass data from the East Coast of the U.S to the Azure server in Asia, it needs through layer after layer of network operators which will cost a lot if time.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 6, 2014 8:02 AM
    Moderator
  • It appears that much of the delay was in communicating with an SQL Server on the other side of the planet.  I tried running the application on an Azure VM in East Asia, and instead of 57 minutes, it took 3 (including the 90 seconds that the stored procedure actually ran).


    Uncaged

    Yes but because of your kind of questions people are mislead. Is that the purpose of your question. Otherwise change the header of that question.


    Success
    Cor

    Tuesday, May 6, 2014 8:07 AM
  • I wasn't trying to be misleading.  At first, I thought it was the large amount of data that was causing ExecuteReader's slowness, not the distance.


    Uncaged

    Wednesday, May 7, 2014 2:39 PM