join on the server or the client?

Actually Steve, I can't imagine why you would want to join on the client.
On the server you have all the benifts of statics, indexes, server grade
memory & cpu & I/O subsystem. Not to mention the SQL Server Buffer cache,
etc.
If you proccess on the client you are going to have to send both full tables
across the network and do something like a hash join on the client.
Have I missed something?
"sklett" <sklett@.mddirect.com> wrote in message
news:OBmjjdQXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> I've got your typical one to many relationship between 2 tables.
> CREATE TABLE tbl_ft_tests (
> ID smallint(6) AUTO_INCREMENT NOT NULL,
> DateCreated datetime NOT NULL
> )
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(11) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL,
> CarrierFreq smallint(6) NOT NULL
> )
> A test will have many segments.
> I'm designing my new sprocs and not sure if it's a better idea to do a
join
> on the server (INNER JOIN tbl_ft_testsegments ON
tbl_ft_testsegments.TestID
> = tbl_ft_tests.ID) then parse out the separate tbl_ft_tests records or if
> it's better to get 2 results sets, one for each table and perform the join
> on the client.
> My gut tells me that I should get 2 result sets and join on the client,
but
> I'm not sure...
> Any guidelines or suggestions?
> Thanks for reading,
> Steve
>I don't think you've missed anything. I will give more details just to be
clear. Once I get the results on the client, I will wrap everything in a
series of classes ("Buiness objects"). So what I have done in the past is
to create all the top level objects (tbl_ft_tests) then I iterate over the
tbl_ft_testsegments results adding each one to the correct top level object.
Something like this:
<pseudo code>
class Test
{
TestSegments[] tests;
}
class TestSegment{}
</pseudo code>
It seems that it would be more graceful to built up the business objects
from the separate tables then to parse through one long collection of
pre-joined results checking for new Tests.
Since I have a 1 to many, I will have results like this:
[ID] [DateCreated] [SegmentID] [CarrierFreq]
1 date 1 123
1 date 2 456
1 date 3 789
2 date 4 111
2 date 5 222
2 date 6 333
It just seems "wrong" to return redundant data (ID, DateCreated, etc)
I hope that makes sense.
Thanks for the post, I'm very interested to know if this new information
sheds more light on my situation.
-SK
"Ryan Hunt" <ryan.hunt@.highwoods.com> wrote in message
news:%23q6iyDRXGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Actually Steve, I can't imagine why you would want to join on the client.
> On the server you have all the benifts of statics, indexes, server grade
> memory & cpu & I/O subsystem. Not to mention the SQL Server Buffer cache,
> etc.
> If you proccess on the client you are going to have to send both full
> tables
> across the network and do something like a hash join on the client.
> Have I missed something?
> "sklett" <sklett@.mddirect.com> wrote in message
> news:OBmjjdQXGHA.3560@.TK2MSFTNGP04.phx.gbl...
> join
> tbl_ft_testsegments.TestID
> but
>|||> It just seems "wrong" to return redundant data (ID, DateCreated, etc)
It seems more wrong (or as Andy Dick would say, "wronger") to use a client
application to do the work that the database was designed to do (which is to
perform joins on relational data and produce a result set).
Of course, you know your application way better than any of us, so by all
means, test it. But 99% of the time, unless the database was designed
poorly or you have a less than optimal data model, it will make more sense
to perform the join in the database.|||Understood, understood.
One more question: If I have multiple 1 to many relationships (A test has
multiple targets and multiple segments) would you still advocate joining it
all on the server?
I want to learn the correct way, I'm just not seeing a clear picture of how
to work with my particular data. Is it poorly designed? I don't think so,
I've normalized it as much as I know how.. and that is what has resulted in
the multiple 1 to many relationships.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e05GJ0RXGHA.196@.TK2MSFTNGP04.phx.gbl...
> It seems more wrong (or as Andy Dick would say, "wronger") to use a client
> application to do the work that the database was designed to do (which is
> to perform joins on relational data and produce a result set).
> Of course, you know your application way better than any of us, so by all
> means, test it. But 99% of the time, unless the database was designed
> poorly or you have a less than optimal data model, it will make more sense
> to perform the join in the database.
>|||> One more question: If I have multiple 1 to many relationships (A test has
> multiple targets and multiple segments) would you still advocate joining
> it all on the server?
YES! As the number of tables in your join goes up, so does the motivation
to keep the join logic on the server!
Use the presentation layer for its intended purpose... to present data, not
to be a relational engine.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uze5QXWXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> YES! As the number of tables in your join goes up, so does the motivation
> to keep the join logic on the server!
> Use the presentation layer for its intended purpose... to present data,
> not to be a relational engine.
OK, so it's common to join everything together on the server, then break it
back apart on the client. I will rethink some of my approaches to things.
The presentation layer doesn't do the work I had discussed, it would all be
done in my DA layer.
Thanks again!