Using SqlDataAdapter or SqlDataReader, which one is more efficient and puts minimum load on server?

Question

I am writing an ASP.net C# program, in which I have to do multiple trips to SQL database and for retriving small small data many times. Please suggest which way is more efficent? Should i use SqlDataAdapter and store entire data in DataTable and retrieve when needed or used SQLDataReader instead, Please suggest any other efficient way if any. thanks!! :)

My guess is lazy loading, though the answer will vary based on what your payload is and what your use case is. — tnw
– tnw, Commented Nov 5, 2013 at 16:19
Why do you need to load "small small data many times" at all? Couldn't you cache that data or load more in one step? Sounds as if you should also swap your business logic into the database instead of using loops. — Tim Schmelter
– Tim Schmelter, Commented Nov 5, 2013 at 16:22
@Tim Schmelter: I have to select one entity from parent table and process its children..i dont see any other way than looping as my knowledge may be limited, if you could suggest me any better way then please do! — love Computer science
– love Computer science, Commented Nov 5, 2013 at 16:31
@loveComputerscience: It's difficult to show a better way since we don't know what you're actually trying to achieve. But you should link parent and child tables via JOIN. — Tim Schmelter
– Tim Schmelter, Commented Nov 5, 2013 at 16:32

D Stanley · Accepted Answer · 2013-11-05 16:29:01Z

1

Neither sounds very good. Getting all of the data and cherry-picking what you need sounds inefficient on both sides (server and clients) while sending lots of tiny sql requests sounds inefficient as well.

Ideally you would get all of the data that you need (but no more) in as few requests as you can. Choosing between SqlDataAdapter and SQlDataReader doesn't solve that for you. You need to look at your design and determine if you can get all of the related data for a page without keeping an in-memory copy of your entire database.

BTW, SqlDataAdapter uses a SqlDataReader internally to get its data.

answered Nov 5, 2013 at 16:29

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SqlDataAdapter or SqlDataReader, which one is more efficient and puts minimum load on server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related