Hi

A few days back I was having a discussion with my colleges on how to fetch data when you want to show category data and also their sub-categories. What I mean is that you have a master table Category (Cat_ID, Cat_Name) and a subcategory table (Subcat_ID, subCat_Name, Cat_ID). Now you want to show category in a gridview and in show the subcategories in the nested grid views inside it.

For this example purpose lets say we have 5 rows in category table and 20 rows in the subcategory table

I know its very simple stuff to be done. But what got into a debate was the fact how to retrieve the data in the best possible way. We had two easy way to fetch data. First was to get all the data from both table and then create a relationship in the Dataset between the tables and bind them. Another way is to use a join in the query and get all the fields from the subcategory table along with the category name from the category table.

[Note: Volume of data means Number of columns * number of rows]

Now when we use the first method we will have to fetch 25 rows from the database (5 rows from the category table and 20 rows from the subcategory table). But the volume of data will be 70(Category (5*2=10) + Subcategory (20 * 3=60)). And the second case we will be retrieving 20 rows only but the volume of data will be 80(20*4=80).

Now you can see that we have pros and cons on both the method. In the first method we are fetching more rows from the database but still the volume of data downloaded is less. While in the second case although the number of rows fetched is less the volume of the data is more.

So which process do you go for while fetching the data from the database?

Hope this helps
Thanks
Vikram