0

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!! :)

love Computer science
  • 1,824
  • 4
  • 20
  • 39
  • My guess is lazy loading, though the answer will vary based on what your payload is and what your use case is. – tnw Nov 05 '13 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 Nov 05 '13 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 Nov 05 '13 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 Nov 05 '13 at 16:32

1 Answers1

2

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.

D Stanley
  • 149,601
  • 11
  • 178
  • 240