Solved

Poor performance with SqlDataAdapter.Fill

Posted on 2009-03-29
4
1,412 Views
Last Modified: 2012-05-06
We are experiencing poor performance with SqlDataAdapter.Fill on a simple, untyped dataset.  Our code is running a query that runs in less than 1 second in SQL Query Analyzer but takes 5-6 seconds when running with SqlDataAdapter.Fill.  The query returns a single small table containing 30-40 rows and 4 columns.

In addition, when running similar queries in parallel from a number of clients, the queries seem to lock each other out for some reason resulting in poor overall performance.  For example, if 5 queries that typically take 10 seconds each are running, all 5 start return from the Fill method after 50 seconds.

We use (NOLOCK) throughout our queries and we have verified that there are no "lock" commands in the C# code that are creating this contention - all threads are "stuck" in the SqlDataAdapter.Fill method for the entire 50 seconds .

Any help would be much appreciated.
0
Comment
Question by:optimaltest
  • 2
4 Comments
 
LVL 4

Expert Comment

by:freer
ID: 24011822
If the table is as small as you say, it should be near-instant. Or is this a small query result from larger tables?

This guy noticed a problem with untyped SQL parameters:

http://www.precariousbalance.com/blogs/archive/2006/01/15/SlowSqlDataAdapterFill.aspx

I'd suggest tracing the SQL to see exactly what is being requested.

Tim
0
 
LVL 4

Accepted Solution

by:
amazingwolf1 earned 500 total points
ID: 24012085
Fot the locks, try using SP_WHO 'active' to see the lock details.

As for the Fill() method:

As you may know SQL Server creates a query plan for a stored procedure when you run it the first time, and this plan is put into cache.
Now, there can be more than one plan for the same procedure, because of the different set options. For a discussion on this see
http://www.karaszi.com/SQLServer/info_sp_recompile_set.asp.

All modern client APIs uses the same SET options by default. Query Analyzer uses a different default on one point: it runs with SET ARITHABORT ON, which a client API does not.

Hope this helped!
0
 

Author Comment

by:optimaltest
ID: 24012590
Thanks for that.  I already saw that article and put the parameters inline, so that isn't the explanation.

I followed your advise and ran the SQL Server Profiler.  It looks like when I run the query from SQL Management Studio, the server is using a different query plan to the one it uses when running from ADO.NET. When running from the Studio, the query performs ~4000 reads.  When running from my app, it performs 1.2 million reads! Any clues?
0
 

Author Closing Comment

by:optimaltest
ID: 31563996
I just added "SET ARITHABORT ON\r\n" before the query string and it works a charm!  Thanks very much amazingwolf1 !!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now