SqlDataAdapter.Fill is too slow even for one record

Question

I have a table with primarykey in MS SQL 2005, which has a few hundred thousand records. When I query it in Management studio for a record, it brings very quickly but when i use code below to find it, it takes many seconds. It is must for me to use dataset as i need to update the row. How can i improve the performance?

objData . ProcName ="myProcName"
objData . CreateCommand()
objData . Parameters("@BName", SqlDbType. VarChar, 20, "MyBranch1")
SqlDataAdapter da = objData . createAdapter()
da . Fill(ds,"MyTable1")

While the proc code is very simple:

select * from MyTable1 Where BranchName = @BName

this dataset is gonna have 5 tables opened the same way so total time is more than a minute

Answer 1

You should consider using datareader instead of dataset and do a manual update using sqlcommand.

U should also consider restricting the number of records you are fetching by possibly supplying additional criteria

Answer 2

I have seen very similar delay behavior using a SQLDataAdapter, which took many seconds to return a response, versus running the exact same procedure in SQL Server Mgmt Studio (on the same client machine), which returned a response instantly.

I rebuilt the indexes on the the affected tables and the response from SQLDataAdapter was then instantaneous.

I have never seen this delay behavior with a SQLDataReader. I often use SQLDataReader and just do a MyTable.Load(MySQLDataReader). It seems to have less overhead than SQLDataAdapter.