Poor performance with SqlDataAdapter.Fill
Posted on 2009-03-29
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.