Getting results from stored procedure very slow from RRS feed

  • Question

  • I am running the following code in a app


    Public Function GetViewDistItemInfoByOrgKey(ByVal OrgKey As Integer, ByVal PriceDate As DateTime) As MarketMasterTesterDataSet.viewDistItemInfoAndDatePriceDataTable


    Dim dtab As New MarketMasterTesterDataSet.viewDistItemInfoAndDatePriceDataTable

    cmdSelPubInfo = New SqlCommand

    cmdSelPubInfo.CommandTimeout = 180

    cmdSelPubInfo.Connection = cnPubs

    cmdSelPubInfo.CommandType = CommandType.StoredProcedure

    cmdSelPubInfo.CommandText = "usp_viewDistributorItems"

    cmdSelPubInfo.Parameters.AddWithValue("@OrgKey", OrgKey)

    cmdSelPubInfo.Parameters.AddWithValue("@PriceDate", PriceDate)

    Dim dataAdapter As New SqlDataAdapter(cmdSelPubInfo)



    Return dtab


    Catch ex As Exception

    Throw ex

    End Try


    End Function


    The function takes over 90 seconds to return the datatable. However, when I run the stored procedure on the Sql server it only takes 4-5 seconds to return results.


    Any ideas as to why this may be taking so long.



    Thursday, March 13, 2008 3:11 PM

All replies

  • The ADO.Net code you've written looks reasonable. The main things that could slow the client-side down are volume of data and unrelated load on the client (memory or cpu pressure from some other thread/process).

    Try looking at server activity via Sql Profiler and compare to what happens when you "run the stored procedure on the Sql Server".  Look for differences in server-side execution times, amount of data returned, query plans, errors, transaction wrapping, etc. Some other sanity checks: how many rows are populated in the dataset? How are you running the stored procedure on the server when it takes only 4-5 seconds -- could you be doing so in a way that doesn't send some of the data over the network?
    Thursday, March 13, 2008 5:28 PM