How to organize dataset queries to improve performance

Question

I don't know when to add to a dataset a tableadapter or a query from toolbox. Does it make any difference?

I also dont know where to create instances of the adapters.

• Should I do it in the Page_Load?
• Should I just do it when I'm going to use it?
• Am I opening a new connection when I create a new instance?

This doesn't seem very important, but every time I create a query a little voice on my brain asks me these questions.

Answer 1

Should I just do it when I'm going to use it?

I would recommend that you only retrieve the data when you are going to use it. If you are not going to need it, there is no reason to waste resources by retrieving it in Page_Load. If you are going to need it multiple times throughout the page load, consider saving the query results to a private variable or collection so that the same data can be reused multiple times throughout the page load.

Am I opening a new connection when I create a new instance?

Asp.net handles connection pooling, and opens and closes connections in an efficient way. You shouldn't have to worry about this.

One other thing to consider from a performance perspective is to avoid using Datasets and TableAdapters. In many cases, they add extra overhead into data retrieval that does not exist when using Linq to Sql, Stored Procedures or DataReaders.

Answer 2

Thanks Yaakov.