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.
lastbattleAsked:
Who is Participating?
 
dunglaConnect With a Mentor Commented:
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.
0
 
JimBrandleyConnect With a Mentor Commented:
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?

Jim
0
 
Rahul Goel ITILConnect With a Mentor Senior 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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
0
 
JimBrandleyConnect With a Mentor Commented:
Can you post the code where you are invoking the DataAdapter.Fill?

Jim
0
 
JimBrandleyConnect With a Mentor Commented:
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.

Jim
0
 
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.

0
 
lastbattleAuthor Commented:
Thanks you so much.
0
 
Luke101Commented:
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.
0
All Courses

From novice to tech pro — start learning today.