troycomp
asked on
Which idea is better returning more than one result set or perform a join
Which approach is better?
Returning Multiple Result sets:
SELECT [CAR_LOAD_ID]
,[CAR_ORDER_FL]
,[CAR_WELL_ID]
,[POSITION_NR]
,[EQUIP_AN]
,[COMMENT_DS]
FROM [CAR_LOAD_T]
where CAR_ID = 2
select [TRAIN_LOAD_ID]
,[TRAIN_ID]
,[TRAIN_TRACK_ID]
,[POSITION_ID]
,[DEST_TRAIN_CODE_ID]
,[LOAD_DT]
,[LOAD_BY_AN]
,[CAR_TRACK_ID]
,[WELL_ORDER_FL]
from [TRAIN_LOAD_T] tl
where CAR_ID = 2
select [CAR_LENGTH_NR]
from CARS_T
where CAR_ID = 2
or perform a join:
SELECT cl.[CAR_LOAD_ID]
,cl.[CAR_ORDER_FL]
,cl.[CAR_WELL_ID]
,cl.[POSITION_NR]
,cl.[EQUIP_AN]
,cl.[COMMENT_DS]
,tl.[TRAIN_LOAD_ID]
,tl.[TRAIN_ID]
,tl.[TRAIN_TRACK_ID]
,tl.[POSITION_ID]
,tl.[DEST_TRAIN_CODE_ID]
,tl.[LOAD_DT]
,tl.[LOAD_BY_AN]
,tl.[CAR_TRACK_ID]
,tl.[WELL_ORDER_FL]
,c.[CAR_LENGTH_NR]
FROM [CAR_LOAD_T] cl
join [TRAIN_LOAD_T] tl
on tl.[CAR_ID] = cl.[CAR_ID]
join CARS_T c
on c.CAR_ID = tl.CAR_ID
where c.CAR_ID = 2
I am using both in my app and i like the multiple result set approach. WAY less C# code to type cause a join returns the same data over and over and i have to check for different values.
Returning Multiple Result sets:
SELECT [CAR_LOAD_ID]
,[CAR_ORDER_FL]
,[CAR_WELL_ID]
,[POSITION_NR]
,[EQUIP_AN]
,[COMMENT_DS]
FROM [CAR_LOAD_T]
where CAR_ID = 2
select [TRAIN_LOAD_ID]
,[TRAIN_ID]
,[TRAIN_TRACK_ID]
,[POSITION_ID]
,[DEST_TRAIN_CODE_ID]
,[LOAD_DT]
,[LOAD_BY_AN]
,[CAR_TRACK_ID]
,[WELL_ORDER_FL]
from [TRAIN_LOAD_T] tl
where CAR_ID = 2
select [CAR_LENGTH_NR]
from CARS_T
where CAR_ID = 2
or perform a join:
SELECT cl.[CAR_LOAD_ID]
,cl.[CAR_ORDER_FL]
,cl.[CAR_WELL_ID]
,cl.[POSITION_NR]
,cl.[EQUIP_AN]
,cl.[COMMENT_DS]
,tl.[TRAIN_LOAD_ID]
,tl.[TRAIN_ID]
,tl.[TRAIN_TRACK_ID]
,tl.[POSITION_ID]
,tl.[DEST_TRAIN_CODE_ID]
,tl.[LOAD_DT]
,tl.[LOAD_BY_AN]
,tl.[CAR_TRACK_ID]
,tl.[WELL_ORDER_FL]
,c.[CAR_LENGTH_NR]
FROM [CAR_LOAD_T] cl
join [TRAIN_LOAD_T] tl
on tl.[CAR_ID] = cl.[CAR_ID]
join CARS_T c
on c.CAR_ID = tl.CAR_ID
where c.CAR_ID = 2
I am using both in my app and i like the multiple result set approach. WAY less C# code to type cause a join returns the same data over and over and i have to check for different values.
chapmandew
It really depends. If youre using the result sets separately, thene get them separately. If you're using them together (a tran_id and a car_well_id at the same time) then join them. Remember, don't pull back fields in the SELECT statement unless you absolutely need them.
ASKER
Im using them together. Whats so "bad" about the multiple result sets? If anything. A join returns more data than multiple result sets cause if a value is found once in table a but table b its found 10 times, you'll get table a results 10 times as well
>>Whats so "bad" about the multiple result sets?
Nothing, really. It all depends on how you want it. If you're not going to need them combined, then don't combine them.
Nothing, really. It all depends on how you want it. If you're not going to need them combined, then don't combine them.
ASKER
Im working on a big project and its due at the end of this month. Im trying to write as little C# code as possible, but i want to make it as efficient as possible. So if im not gonna take a performance hit for not joining then i'm gonna use result sets. Will i take a performance hit for this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your input