Solved

Poor performance with SqlDataAdapter.Fill

Posted on 2009-03-29
4
1,442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IIS Authentication Error 401 16 74
Blending HTML output from R Studio into a ASP.Net/C# project 4 52
Run software updates from the website 6 50
C# LINQ query question 11 33
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!
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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