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

Kegler

Datareader is def the way to go: msdn.microsoft.com/en-us/library/haa3afyz%28v=VS.100%29.aspx – Myatt

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

Ecdysis

So are you saying that its not possible to have better performance with dataset? And I am using enough criteria as every one of 5 datatables in dataset does not contain more than 5 rows – Kegler

when u use datareader, it moves one record at a time while incase of dataadapter and dataset, adapter just fetches all records and creates datarow objects for it (and constraints and relationships as well, depending on if its typed dataset or not) this object creation takes more time which is directly proportional to number of records u have. Is that a webapp or windows app? i might be able to tell u the exact approach to take depending upon what you are working on – Ecdysis

This is webapp and I was wishing to improve the performance without changing much of a code. But if it is not possible then i surly will goto change it to data reader. You are right about DataRow object and etc. Can we set some attribute or something to ignore constraint and relation stuff in dataset? – Kegler

i remember there was a property in dataset called ignoreconstraints. Check that out and confirm. I have been using domain model for ages so have started forgetting about dataset a little – Ecdysis

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.

Upmost