I've got a dataset inside a Visual Studio 2010 Web Application project which accesses the DB having a complex SQL statement. Basically run the statement in SQL Management Studio directly, it loads inside a under another. You can definitely, I run it while using "Preview Data" button within the dataset designer, or I attempt to gain access to it on the page (having a gridview for instance), it requires over 40 seconds!

What steps must i decide to try find what's leading to this massive delay whenever using the dataset?

You will find two cases:

  • Issue is on Application Level
  • Issue is on Database level, really a SQL query itself

In order initial step attempt to exclude among the cases, much simpler from the perspectives would be to debug SQL side:

  1. Run Sql Profiler
  2. Run query from Management Studio
  3. Save profiler logs
  4. Obvious profiler logs
  5. Run "Preview DataSet"
  6. Compare execution logs and find out whether any difference in SQL

Some steps to follow along with

Put a sql profiler towards the database server to be certain what sql command the application is performing

The dataset class has inadequate performance, you should attempt make use of a datareader

Use a stop-watch instance to consider diferent occasions to determinate where would be the slow lines of code