Solved

Poor performance with SqlDataAdapter.Fill

Posted on 2009-03-29
4
1,434 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP.net Markup for Sub Menu 2 40
Securing WEBAPI on Azure 2 22
Web page design problem 3 14
Inserting LocalDB Table to SQL Server C# 3 15
Running classic asp applications under Windows Server 2008 R2 (x64) and IIS 7 is not as easy as one may think. It took me a while to figure it out while getting error 8002801d a few times. After you install the OS you will need to install the fol…
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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