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?

Dirk Boer
  • 365
  • 1
  • 10
  • If you are basically asking about TDS protocol compression, see this answer. – Dan Guzman Nov 01 '23 at 16:27
  • I added an example - I think it's something else. – Dirk Boer Nov 01 '23 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. – ypercubeᵀᴹ Nov 01 '23 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 01 '23 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. – ypercubeᵀᴹ Nov 03 '23 at 17:02
  • This specifically: ON TableB.Id = TableA.TableBId. If the join was the other way around yes, it would make sense (ON TableB.TableAId = TableA.Id) – ypercubeᵀᴹ Nov 03 '23 at 17:10
  • you could think that if the SQL Connection Client and SQL both take this into account they create something that optimizes this. On the simplest level "LargeTextA will be repeated 1.000.000 times" instead of all sending them over the wire. – Dirk Boer Nov 04 '23 at 23:40

1 Answers1

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.

simon coleman
  • 266
  • 1
  • 6
  • 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. – Grant Fritchey Nov 02 '23 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 02 '23 at 13:53