Solved

Poor performance with SqlDataAdapter.Fill

Posted on 2009-03-29
4
1,405 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I just added "SET ARITHABORT ON\r\n" before the query string and it works a charm!  Thanks very much amazingwolf1 !!!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

10 Experts available now in Live!

Get 1:1 Help Now