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.
Remember to mark as answer if this post answered or solved your problem.
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?
Remember to mark as answer if this post answered or solved your problem.
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!
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...).
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.
ywb
Member
142 Points
80 Posts
ExecuteDataSet Slow?
Feb 15, 2012 11:46 PM|LINK
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
b471code3
Star
13877 Points
2598 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 02:23 AM|LINK
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.
ywb
Member
142 Points
80 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 06:34 AM|LINK
Hi, thank you for your reply. However, I don't see any performance improvement after changing the syntax...
cnranasinghe
Star
8899 Points
1800 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 09:38 AM|LINK
I guess this is due to network bandwidth or weight of data. Or try remove all EL dll libraries and refer them again.
ywb
Member
142 Points
80 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 05:32 PM|LINK
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...?
b471code3
Star
13877 Points
2598 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 10:08 PM|LINK
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?
ywb
Member
142 Points
80 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 10:35 PM|LINK
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!
b471code3
Star
13877 Points
2598 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 10:49 PM|LINK
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?
ywb
Member
142 Points
80 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 11:06 PM|LINK
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...).
ywb
Member
142 Points
80 Posts
Re: ExecuteDataSet Slow?
Feb 16, 2012 11:41 PM|LINK
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.