1

If I have a Book table and Author table, with Book having a foreign key to Author, many rows in Book may have the same author. If I then run this query:

SELECT * FROM Book
INNER JOIN Author ON Author.Id = Book.AuthorId
WHERE Author.LastName = 'Shakespeare'

how will the underlying data be formatted and sent to the client? In essence - will each Book row have it's own Author (i.e. the data from Author is duplicated for each row), or will there be one single Author entry (representing Shakespeare)?

UPDATE:

To be clear, I just want to know whether joining tables will increase packet size (and transmission time) significantly, or if the protocol has some compression to ensure that only unique entries are transmitted.

sondergard
  • 215
  • 2
  • 6
  • I don't really want to dig into the protocol details (as it is quite comprehensive) - I was hoping someone knew :). But thanks for the link though, now I at least know where to look. – sondergard Aug 07 '14 at 13:13
  • Well, we are using Entity Framework, and I want to get a feel of what the cost is of joining tables (and selecting *). E.g. if my query results in 10 million books (yes, Shakespeare was VERY productive!), I would have to transmit 20 million entries (10 m books + 10 m authors). That seems like a big overhead. Then it might be better to load the books first, and subsequently load the distinct authors. Or is it generally just negligable? – sondergard Aug 07 '14 at 13:26
  • @sondergard if your result set is over a certain amount I would NOT return the whole result set and would instead ask the user to trim that down a bit OR use server side paging. Also, EF is so inefficient when it comes to queries that it's not going to matter on small tables and there is a better chance of your query taking forever on larger result sets with EF that you'll need to use paging anyway. – Sean Gallardy Aug 07 '14 at 14:15

1 Answers1

1

It doesn't look like there is compression like you are talking about in the TDS

A question that might help you: Is data retrieved from SQL Server compressed for transmission?

Community
  • 1
JustinDoesWork
  • 478
  • 1
  • 5
  • 14
  • Ok so what comes out in e.g. SQL management studio is the actual data structure (a simple table). There is no underlying structure which will send only unique instances of each row? – sondergard Aug 07 '14 at 12:07
  • There are operators such as GROUP BY and DISTINCT which can help you with returning only unique instances. – RLF Aug 07 '14 at 13:10
  • @sondergard yes there is no underlying compression. – JustinDoesWork Aug 07 '14 at 13:40