External Link Performance

Question

Why are external connections generally slower than internal connections? I mean, regardless of the database provider. I assume this is an implementation issue or an access plan, but I could not convince my colleague who believes that the performance should be the same.

Thanks in advance Louis

Answer 1

An inner join will delete rows, but outer join not. This leads to a significant increase in the number of rows.

Check out this article that visually describes joins. Notice how much less the result set is for inner join versus left and full outer join . These photos do not represent each scenario, but they give you an idea of ​​what is happening.

Answer 2

I had a performance issue on my site and found a request for 7 seconds to complete

All columns in this query were numbered and indexed (SQL Server 2008 R2)

This complete outer join was between 3 tables with (150, 350 and 270) thousand rows each.

I just replaced the FULL OUTER JOIN for the LEFT JOIN and the runtime was reduced to 0 seconds (miliseconds)

Before I just checked a few rows Then, if the tables contain thousands or millions of records, the performance of a full outer join will not be good.

Answer 3

An outer join usually gives you MANY results ( A*B instead of WHERE A=B ). It will take more time.

Answer 4

In general, this is due to the fact that the db engine must perform much more comparison operations in order to narrow down the result set.

Answer 5

Test it with real data. Maybe if the outer join doesn't introduce extra lines, the performance will be the same.

Answer 6

Several times when you leave a connection, they are faster than internal connections. it will depend on the two result sets that need to be connected