Avatar of netsmithcentral
netsmithcentral
Flag for United States of America asked on

How to debug DataSet performance

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?
.NET ProgrammingMicrosoft SQL Server 2008Visual Basic.NETASP.NETWeb Development

Avatar of undefined
Last Comment
Jacques Bourgeois (James Burger)

8/22/2022 - Mon
Miguel Oz

Check Tips for Database Access at:
http://msdn.microsoft.com/en-us/library/ms973839.aspx

Turn off the features that you do not need and define your primary key comes to mind
vasto

When you run the query In SQL Management studio you will see results while the query is still working. In the right bottom corner you can see how much time it takes to finish the query. Wait until you see "Query executed successfully" in the bottom left corner.
Nasir Razzaq

Try to use untyped dataset to see if it speeds up things

Dim dbadp As New SqlClient.SqlDataAdapter("Your sql query", "your connection string")
Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose
GridView1.DataSource = dTable
GridView1.DataBind()
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jacques Bourgeois (James Burger)

You need to trigger an asynchronous query. This is not built in when you use the Fill command, but it can be set up using a DataReader to fill in the DataSet.

There is a good sample of the technique here.
ASKER CERTIFIED SOLUTION
x77

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jacques Bourgeois (James Burger)

Comment on 4 in the accept solution.

One has to be careful about calling AcceptChanges. After AcceptChanges has been called on a DataRow or a DataTable, that row on table won't be considered when doing an Update on the Table.

Also, since the original values will be destroyed, you lose the best way to detect changes made by other users and solve concurrency issues.