locked
ExecuteDataSet Slow? RRS feed

  • Question

  • User-1170096362 posted

    Hi,

    I use a method similar to the following to execute a stored procedure and return a DataSet.


            public DataSet MyMethod(string param1, string param2)
            {
                Database myDB = DatabaseFactory.CreateDatabase("ConnString");

                string sqlCommand = "stp_myStoredProc";
                DbCommand dbCommand = myDB.GetStoredProcCommand(sqlCommand);

                myDB.AddInParameter(dbCommand, "Parameter1", DbType.String, param1);
                myDB.AddInParameter(dbCommand, "Parameter2", DbType.String, param2);

                return myDB.ExecuteDataSet(dbCommand);
            }

    This method takes over 1.5 minutes to return the DataSet. However, when I excute the stored procedure directly using SQL Management Studio like this:

        EXEC stp_myStoredProc 'abc', 'def';

    it took only 1 second to fetch the data (a total of 9 rows).

    What can be causing the delay and how can I speed up this method?


    Thanks,
    ywb

    Wednesday, February 15, 2012 7:46 PM

Answers

  • User-37275327 posted

    As i said earlier this may due to network bandwidth.Better monitor it for a while.

    http://www.microsoft.com/download/en/details.aspx?id=4865

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 16, 2012 10:31 PM

All replies

  • User-1226263862 posted

    Try changing your syntax a bit to this:

    public DataSet MyMethod(string param1, string param2)
    {
        Database myDB = DatabaseFactory.CreateDatabase("ConnString");
        // create an object array of the parameters to use in the query
        Object[] sqlParams = new Object[] { param1, param2 };
        // call the stored procedure passing in the parameters.
        return = myDB.ExecuteDataSet("stp_myStoredProc", sqlParams);
    }
    

    I use this library and the new version even reduces the code more to 2 lines vs. the 3 I showed. You might take a look.

    Wednesday, February 15, 2012 10:23 PM
  • User-1170096362 posted

    Hi, thank you for your reply. However, I don't see any performance improvement after changing the syntax...

    Thursday, February 16, 2012 2:34 AM
  • User-37275327 posted

    I guess this is due to network bandwidth or weight of data. Or try remove all EL dll libraries and refer them again.

    Thursday, February 16, 2012 5:38 AM
  • User-1170096362 posted

    Hi cnranasinghe, thank you for the suggestion. I tried removing the Enterprise Library DLLs and adding the reference again but it is still the same.

    If my stored procedure only took 1, 2 seconds to fetch the data, where was the rest of the 1.5 minutes spent on...?

    Thursday, February 16, 2012 1:32 PM
  • User-1226263862 posted

    First create a test page and put a simple SqlDataSource on it and perform a simple query using that stored procedure and passing in some default values and see if you still have the problem.  This way you can eliminate the problem with the Enterprise Library. 

    Second debug your code and see where the hangup is.  There could be a problem with authentication or a connection string issue, although if it eventually gets the data thats highly unlikely.  Could it be a performance or connectivity issue with your webserver and your sql server?

    Thursday, February 16, 2012 6:08 PM
  • User-1170096362 posted

    Yesterday I did build a test page that uses a DataAdapter to call that same stored procedure and create the DataSet. That also took ~1.5 minutes to complete. So you were right, the problem is not limited to the Enterprise Library.

    I also don't think it has to do with connectivity between my web server and the db server. They are both on Azure... And all my other pages work fine with my entire data access layer uses Enterprise Library.

    This is a head scratcher. Thank you for your advice!

    Thursday, February 16, 2012 6:35 PM
  • User-1226263862 posted

    Don't know much about Azure but that could be the issue as well.  Does that page have anything else on it or require any specific authentication?

    Thursday, February 16, 2012 6:49 PM
  • User-1170096362 posted

    It doesn't. Anyway I built another test page with nothing but a GridView and I tried populating it using Enterprise Library and a DataAdapter and both gave similar performance (or lack of...).

    Thursday, February 16, 2012 7:06 PM
  • User-1170096362 posted

    I just noticed that the slow performance is not consistent. This afternoon I went to check that page and it loaded within 2 seconds. I refreshed it a few times and it reloaded within 1 or 2 second. And I didn't make any change to the page. I checked again a couple of ours later and it's back to 1.5 minutes again.

    I also found that when it is slow, it is slow too on my development environment (both connecting to the same SQL Azure db). So seems like it not Windows Azure related.

    Thursday, February 16, 2012 7:41 PM
  • User-1226263862 posted

    Then it sounds like it could be an IIS issue with the application stopping and restarting adn recompiling the website if any code changes in the app_code or the web.config file changes.

    Thursday, February 16, 2012 7:44 PM
  • User-37275327 posted

    As i said earlier this may due to network bandwidth.Better monitor it for a while.

    http://www.microsoft.com/download/en/details.aspx?id=4865

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 16, 2012 10:31 PM