Solved

Poor performance with SqlDataAdapter.Fill

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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 …

791 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