1

Say I have:

  • very wide table A where I need all columns
  • that I need to join with a small table B that has a LOT of rows.

Of course when I join these two tables I get returned all the content of A for every row of B.

Is this internally being optimized away or does all this data being sent over the wire?

Because if it's the last thing it takes me very little effort to the join manually in memory in this very specific case.

Example:

SELECT ColumnA1, ColumnA2, ColumnA3, ColumnB1 
FROM TableA 
JOIN TableB ON TableB.Id = TableA.TableBId

Resulting in data:

ColumnA1    ColumnA2    ColumnA3        ColumnB1 
------------------------------------------------
LargeTextA  LargeTextB  LargeTextC      1
LargeTextA  LargeTextB  LargeTextC      2
LargeTextA  LargeTextB  LargeTextC      3
LargeTextA  LargeTextB  LargeTextC      [1.000.000 times more]

Will LargeTextA, LargeTextB and LargeTextC being transmitted 1.000.000 times over the line or will it only be sent once because it will know it will just be repeated data?

7
  • If you are basically asking about TDS protocol compression, see this answer.
    – Dan Guzman
    Nov 1, 2023 at 16:27
  • I added an example - I think it's something else.
    – Dirk Boer
    Nov 1, 2023 at 16:54
  • Assuming that TableB (id) is the primary key of tableB, I don't see how the data from A will be repeated. The result should be all the rows from A, or less. The data from ColumnB1 may be repeated yes, as a row in B can be associated with many rows from A. Nov 1, 2023 at 17:32
  • Hi @ypercurbe, see the example - assuming ColumnB refers always to the same ColumnA - this case happens in i.e. in ORMs where you do db.TableAs.Include(a => a.TableBs).ById(1)
    – Dirk Boer
    Nov 1, 2023 at 18:23
  • I saw the example but it still does not make any sense. If TableA already has duplicate data, of course they will transmitted many times, as many as they are duplicated. Nov 3, 2023 at 17:02

1 Answer 1

2

It will repeat the content. SQLServer does not try to second guess why you wanted all this data, nor does it try to interpolate to reduce the overall volume sent.

If you join tables together & over partial keys (ie, not 1:1) then you will get this multiplication effect (ie, 1:n) per row in table A.

If you are getting a lot of whole result rows duplicated just because the join predicate is partial then DISTINCT will help reduce a little: eg, rowA1 rowB1 rowA1 rowB1 which becomes just rowA1 rowB1 But all the fields need to be the same in both rows for that to occur.

I suspect you are really have a join predicate giving you 1: and there is not a lot you can do about that.

You might find performance is better by returning an initial rowset which is just: rowApk rowBpk

Then requerying for the specific rows in tableA/tableB, rather than trying to consume an entire large rowset in one go.

If it becomes a pagination issue, either look at the client side options to cursor the data (rather than trying to consume the whole resultset) or put the results in another object (work table in tempdb, # table etc) and retrieve in batches from there.

Most likely, you should look at how to improve the table structures to help streamline what you need to return.

2
  • Good answer. One detail to add is that question of where the memory is used for the join operation itself. That will be done in Azure, then all the results will go over the wire as you described well. Nov 2, 2023 at 13:43
  • great answer! thanks a lot! seems it does pay off to sometimes do two seperate queries and join the entities in memory on the app side.
    – Dirk Boer
    Nov 2, 2023 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.