SqlDataAdapter.Fill is too slow even for one record

.net performance sqldataadapter
Adeem picture Adeem · Jan 4, 2012 · Viewed 7.1k times · Source

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

Just Me picture Just Me · Jan 4, 2012

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

Related questions

Best way to reverse a string

I've just had to write a string reverse function in C# 2.0 (i.e. LINQ not available) and came up with this: public string Reverse(string text) { char[] cArray = text.ToCharArray(); string reverse = String.Empty; for (int i = cArray.Length - 1; …

Read More
How to create a new object instance from a Type

One may not always know the Type of an object at compile-time, but may need to create an instance of the Type. How do you get a new object instance from a Type?

Read More
What's the fastest way to read a text file line-by-line?

I want to read a text file line by line. I wanted to know if I'm doing it as efficiently as possible within the .NET C# scope of things. This is what I'm trying so far: var filestream = new System.…

Read More