join table by datarelation or through query 1

[tekkerguy]

I'm writing a web application, that's not user or record intensive. For a given form, I need to pull up a record, and certain fields will be from one table, and other fields from other tables, join by primary/foreign keys.

My question is, is it easier to get the data directly from the sql statement, doing the join within it, and returning it to a table in a dataset, or is it better to pull each table into the dataset, relate them with a datarelation, and populate from that?

Keep in mind that a user will be paging through the records, and editing them as they find the one that needs editing.

Thanks for any help.

 

[jmeckley]

it depends on the situation. it is a good idea to cache values that do not change often (lookup values). It's not a good idea to cache values that are volital, like customer orders.

I would recomend quering the db for the records you need and populating a dataset. so if there are 2 tables that relate in you db, create a dataset with 2 datatables (and relation) and populate each with the subset of data required by the user for the current request.

for best preformance you will need to query not only the current required data, but also the total number of records. There is a preformance loss if you only need a page of 10 records, but there are 1,000 in the db and you pull all 1,000.

I don't use the datasource controls provided in asp.net. but I do know the sql datasource doesn't optimise paging. I believe the objectdatasource does. if neither do, you could also implement your own datasource whereby you pass in the datasource the current page of data and the total number of records match the predicate.

Jason Meckley
Programmer
Specialty Bakers, Inc.