How to debug DataSet performance

Question

I have a dataset in a Visual Studio 2010 Web App project which accesses the DB with a complex SQL statement. If I run the statement in SQL Management Studio directly, it loads in a less than a second. If however, I run it using the "Preview Data" button in the dataset designer, or I try to access it on a page (with a gridview for example), it takes over 40 seconds!

What steps should I take to track down what's causing this huge delay when working with the dataset?

Answer 1

There are two cases:

• Problem is on Application Level
• Problem is on Data Base level, actually a SQL query itself

So as first step try to exclude one of the cases, much easier from my perspectives is to debug SQL side:

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