How to speed up DataAdapter.Fill Method?

I am facing slowness in using DataAdapter.Fill method in order to populate DataSet with data. The base table has around 10000 records. But I am not getting all of the records from the table. I am just getting only one record from the table by specifying WHERE condition in SELECT statement.

How can I improve the performace of DataAdapter.Fill method? Or Is there any other way I can fill up datasets with data retrieved from the database with High performance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

the only one way is optimize your query string.
If you just want to retrieve only one record, use where clause in your query.
One more thing, just select the column that you want to use, select many column will slowdown the performance.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Can you post the code that's doing the load, along with your select statement, and a description of the table in the database with any constraints and indexes?

Rahul Goel ITILSenior Consultant - DeloitteCommented:
You need to introduce some new indexes by omptimizing the query and looking into the execution plan.

Secondly, you need to fine tune the query.

I hope this will help.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

lastbattleAuthor Commented:
My SELECT statement is as simple as below.

SELECT column1,column2,column3,column4,column5 FROM TableA WHERE column1='SampleID'

I have index on column1
Can you post the code where you are invoking the DataAdapter.Fill?

Two observations:
1. A surprisingly large portion of the time required to service a request to a database is spent developing an execution plan. Those plans can be reused, but only if you use parameterized queries. When you imbed 'SampleID' in your query, selects using a different value for column1 will be required to develop the plan all over again. For Oracle, that statement would look like this when parameterized:
SELECT column1,column2,column3,column4,column5 FROM TableA WHERE column1= :p1

and for SQL Server,
SELECT column1,column2,column3,column4,column5 FROM TableA WHERE column1= @col1Value

If you elect to run your query this way, you need to add a parameter to the command object Parameters collection.

2. In most all modern databases, 10000 rows is considered to be a small table. So, selects should be pretty fast. In Oracle, you need to make sure you have current statistics on that table, or it will not be able to use the index for the select, and will instead use a full table scan each time.

lastbattleAuthor Commented:
I found the root cause of dataadapter.fill method slowness. The query is slow as I have included Convert function on the left side of WHERE clause condition like below

SELECT column1,column2,column3,column4,column5 FROM TableA WHERE Convert(varchar,column1)= @col1Value.

Thanks for all of your comments and suggestions.

lastbattleAuthor Commented:
Thanks you so much.
If it is possible I would not use the DataSet it is a very heavy object. Use the DataTable instead or better yet use nested ArrayLists with string arrays for your columns if you want maximum performance. I have done extensive benchmark tests and found that nested ArrayLists with regular one dimensional Arrays to represnet columns performs beatifully. It will make your code slightly more complicated but the performance pay off is well worth it. I dont have to use data caching anymore.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.