Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akashenk
Posting Yak Master
111 Posts |
 Posted - 2006-10-15 : 20:59:42
|
I have the following three tables:People: Person_ID, PersonNameEmails: Email_ID, Person_ID, AddressPhoneNumbers: Phone_ID, Person_ID, NumberA single person can have multiple records in the Emails and PhoneNumbers tables. If I wish to retrieve a person and all their contact info, I can use a stored procedure that contains a join like:SELECT p.Person_ID AS PersonId, p.PersonName, e.Email_ID, e.Address, ph.Phone_ID, ph.Number FROM (People p LEFT OUTER JOIN Emails e ON (p.Person_ID=e.Person_ID)) LEFT OUTER JOIN PhoneNumbers ph ON (p.Person_ID=ph.Person_ID) WHERE p.Person_ID = someparameter This will return a single resultset. In the case of a person having a single email address and phone numner, the resultset will have one record.Alternatively, I can return three separate resultsets (all in the same call to the db) using the following in the stored procedure:SELECT p.Person_ID, p.PersonName FROM People WHERE Person_ID=someparameterSELECT Email_ID, Person_ID, Address FROM Emails WHERE Person_ID=someparameterSELECT Phone_ID, Person_ID, Number FROM PhoneNumbers WHERE Person_ID=someparameter My question is, which of these is more efficient. Obviously, if the person only has a single email and phone number, the join would be more efficient, but if they have multiple records in those tables, wouldn't the join return unnecessary data? For instance, if they had 2 phone numbers and 2 email addresses, the join would return a resultset with 4 records, but there would be duplicate data in each record. (for example, the PersonName field would be the same for all four records). This might not be an issue for my simple example, however, at some point (when returning records for many People) wouldn't the additional unecessary data cause transmission performance issues? Is there a point at which one is better than the other, or is one always better than the other as far as performance is concerned? I think the client App's processing requirements for each method is probably about the same.Thanks in advance!Al |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-15 : 22:20:37
|
"My question is, which of these is more efficient. "JOIN method. Only single read to the database
KH |
 |
|
|
akashenk
Posting Yak Master
111 Posts |
Posted - 2006-10-15 : 22:35:50
|
| Are you referring to the database scan itself or to the call to the database from the client application? Both of the methods I mentioned use a single database connection and call. If you are referring to the scan, then I will assume you mean that the performance savings of doing a join far outweigh the transmission savings of returning less data. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-15 : 23:35:48
|
| It's much desired to have as less database IO as possible. In your case, data tranmitted over the network does not seem to be huge, so its better to use JOIN as KHTan suggested. Also, handling of multiple resultsets will be tedious job.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
|
|
|
|
|
Join Vs. Multiple ResultSets