MBR IT/.NET 247 : NOT ANSWERED BEFORE: Data Sets and Data Grids with relational tables - Use a join? on ASPFriends.com 'aspngescalate' list

NOT ANSWERED BEFORE: Data Sets and Data Grids with relational tables - Use a join?

Messages

Related Types

This message was discovered on ASPFriends.com 'aspngescalate' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Jesse Liberty (VIP)

I tried the following question on ASP Data and ASP Controls. I did get one
reply (from Alex Lowe) who suggested that option 1 will exhibit better
performance (I suspect strongly that he is right). Is there any reason to
prefer option 2?

Suppose I have two tables: one orders, the other products. I want to show
the orders in a data grid. Within an order, there are products identified by
product id, and the detail (e.g., the product name) is in the products
table. I have two choces:

Option 1) In my data set, I can create a command with a join on the tables,
e.g., "Select o.customerName, o.date, p.productDescription from orders o
join products p on o.productID &# p;.productID." This will return a single
table with all the data I need with which I can then use to fill the data
set

Option 2) In my data set I can create two commands to create two tables. The
first with the orders and the second with the products, and then I can
create a data relation between them and try to teach the data grid which
table to use for each field it displays.

Which is the preferred method for handing this kind of relational data to a
data grid in a web form? It would seem that option 1 is much easier, however
is there a reason to prefer option 2?

-------------------------------
Jesse Liberty
Liberty Associates, Inc.
.NET Training & Development
http://www.LibertyAssociates.com

Reply to this message...

Banta Singh

Hi Jesse:

I would prefer case 2 when both the table are *very huge*. If I will create a join then it will take lot of time for my query to get executed, but in the second case this time is not much because of relation.

Banta
----- Original Message -----
From: Jesse Liberty
To: aspngescalate
Sent: Wednesday, July 11, 2001 2:07 PM
Subject: [aspngescalate] NOT ANSWERED BEFORE: Data Sets and Data Grids with relational tables - Use a join?

I tried the following question on ASP Data and ASP Controls. I did get one reply (from Alex Lowe) who suggested that option 1 will exhibit better performance (I suspect strongly that he is right). Is there any reason to prefer option 2?

Suppose I have two tables: one orders, the other products. I want to show the orders in a data grid. Within an order, there are products identified by product id, and the detail (e.g., the product name) is in the products table. I have two choces:

Option 1) In my data set, I can create a command with a join on the tables, e.g., "Select o.customerName, o.date, p.productDescription from orders o join products p on o.productID = p.productID." This will return a single table with all the data I need with which I can then use to fill the data set

Option 2) In my data set I can create two commands to create two tables. The first with the orders and the second with the products, and then I can create a data relation between them and try to teach the data grid which table to use for each field it displays.

Which is the preferred method for handing this kind of relational data to a data grid in a web form? It would seem that option 1 is much easier, however is there a reason to prefer option 2?

-------------------------------
Jesse Liberty
Liberty Associates, Inc.
.NET Training & Development
http://www.LibertyAssociates.com

| [aspngescalate] member Click here to reveal e-mail address = YOUR ID | http://www.asplists.com/asplists/aspngescalate.asp = JOIN/QUIT

Reply to this message...

Alex Lowe

I disagree that using a ADO.NET's DataRelation to join *very huge* tables would be any faster than a JOIN inside a DBMS. Using a JOIN in the database provides you with a relation (PK/FK relationships) that can be established before executing any SQL. In addition to the table relationships, the DBMS could also have indexes that will increase performance when joining tables.

Jesse, maybe you are looking for something else with regard to option 2 but in my first email I mentioned that the DataRelation would be ideal for joining data from two different (SQL Server and Oracle for example) datastores where a SQL JOIN might not be possible. In addition to that reason, you may choose Option 2 if you want to use the two tables together and individually in the same page.

Alex - Asplists.com Moderator
http://www.aspalliance.com/aldotnet/
http://www.asp-grandrapids.net

---- Original Message -----
From: Banta Singh
To: aspngescalate
Sent: Wednesday, July 11, 2001 5:06 PM
Subject: [aspngescalate] Re: NOT ANSWERED BEFORE: Data Sets and Data Grids with relational tables - Use a join?

Hi Jesse:

I would prefer case 2 when both the table are *very huge*. If I will create a join then it will take lot of time for my query to get executed, but in the second case this time is not much because of relation.

Banta
----- Original Message -----
From: Jesse Liberty
To: aspngescalate
Sent: Wednesday, July 11, 2001 2:07 PM
Subject: [aspngescalate] NOT ANSWERED BEFORE: Data Sets and Data Grids with relational tables - Use a join?

I tried the following question on ASP Data and ASP Controls. I did get one reply (from Alex Lowe) who suggested that option 1 will exhibit better performance (I suspect strongly that he is right). Is there any reason to prefer option 2?

Suppose I have two tables: one orders, the other products. I want to show the orders in a data grid. Within an order, there are products identified by product id, and the detail (e.g., the product name) is in the products table. I have two choces:

Option 1) In my data set, I can create a command with a join on the tables, e.g., "Select o.customerName, o.date, p.productDescription from orders o join products p on o.productID = p.productID." This will return a single table with all the data I need with which I can then use to fill the data set

Option 2) In my data set I can create two commands to create two tables. The first with the orders and the second with the products, and then I can create a data relation between them and try to teach the data grid which table to use for each field it displays.

Which is the preferred method for handing this kind of relational data to a data grid in a web form? It would seem that option 1 is much easier, however is there a reason to prefer option 2?

-------------------------------
Jesse Liberty
Liberty Associates, Inc.
.NET Training & Development
http://www.LibertyAssociates.com

| [aspngescalate] member Click here to reveal e-mail address = YOUR ID | http://www.asplists.com/asplists/aspngescalate.asp = JOIN/QUIT
| [aspngescalate] member Click here to reveal e-mail address = YOUR ID | http://www.asplists.com/asplists/aspngescalate.asp = JOIN/QUIT

Reply to this message...

Jesse Liberty (VIP)

Thanks.

I am just looking for someone at Microsoft to chime in if they have anything
else in mind. I suspect, however, that your assessment is correct.

Thanks.

-j

-------------------------------
Jesse Liberty
Liberty Associates, Inc.
.NET Training & Development
http://www.LibertyAssociates.com

-----Original Message-----
From: Alex Lowe [mailto:Click here to reveal e-mail address]
Sent: Wednesday, July 11, 2001 5:28 PM
To: aspngescalate
Subject: [aspngescalate] Re: NOT ANSWERED BEFORE: Data Sets and Data Grids
with relational tables - Use a join?

I disagree that using a ADO.NET's DataRelation to join *very huge* tables
would be any faster than a JOIN inside a DBMS. Using a JOIN in the database
provides you with a relation (PK/FK relationships) that can be established
before executing any SQL. In addition to the table relationships, the DBMS
could also have indexes that will increase performance when joining tables.

Jesse, maybe you are looking for something else with regard to option 2
but in my first email I mentioned that the DataRelation would be ideal for
joining data from two different (SQL Server and Oracle for example)
datastores where a SQL JOIN might not be possible. In addition to that
reason, you may choose Option 2 if you want to use the two tables together
and individually in the same page.

Alex - Asplists.com Moderator
http://www.aspalliance.com/aldotnet/
http://www.asp-grandrapids.net

---- Original Message -----
From: Banta Singh
To: aspngescalate
Sent: Wednesday, July 11, 2001 5:06 PM
Subject: [aspngescalate] Re: NOT ANSWERED BEFORE: Data Sets and Data
Grids with relational tables - Use a join?

Hi Jesse:

I would prefer case 2 when both the table are *very huge*. If I will
create a join then it will take lot of time for my query to get executed,
but in the second case this time is not much because of relation.

Banta
----- Original Message -----
From: Jesse Liberty
To: aspngescalate
Sent: Wednesday, July 11, 2001 2:07 PM
Subject: [aspngescalate] NOT ANSWERED BEFORE: Data Sets and Data Grids
with relational tables - Use a join?

I tried the following question on ASP Data and ASP Controls. I did get
one reply (from Alex Lowe) who suggested that option 1 will exhibit better
performance (I suspect strongly that he is right). Is there any reason to
prefer option 2?

Suppose I have two tables: one orders, the other products. I want to
show the orders in a data grid. Within an order, there are products
identified by product id, and the detail (e.g., the product name) is in the
products table. I have two choces:

Option 1) In my data set, I can create a command with a join on the
tables, e.g., "Select o.customerName, o.date, p.productDescription from
orders o join products p on o.productID &# p;.productID." This will return a
single table with all the data I need with which I can then use to fill the
data set

Option 2) In my data set I can create two commands to create two
tables. The first with the orders and the second with the products, and then
I can create a data relation between them and try to teach the data grid
which table to use for each field it displays.

Which is the preferred method for handing this kind of relational data
to a data grid in a web form? It would seem that option 1 is much easier,
however is there a reason to prefer option 2?

-------------------------------
Jesse Liberty
Liberty Associates, Inc.
.NET Training & Development
http://www.LibertyAssociates.com

| [aspngescalate] member Click here to reveal e-mail address &# Y;OUR ID |
http://www.asplists.com/asplists/aspngescalate.asp &# J;OIN/QUIT
| [aspngescalate] member Click here to reveal e-mail address &# Y;OUR ID |
http://www.asplists.com/asplists/aspngescalate.asp &# J;OIN/QUIT
| [aspngescalate] member Click here to reveal e-mail address &# Y;OUR ID |
http://www.asplists.com/asplists/aspngescalate.asp &# J;OIN/QUIT

Reply to this message...

System.Data.DataRelation

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com