Do cross-joins get optimised by the client library?

Question

Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.

I came across Joins are for lazy people on SO, and it's made me question an assumption I'd made.

Joining in the app server can be more efficient if joining on the database causes severe redundancy in the result set sent over the network. Consider tables A and B, where each row in A is associated with 20 rows in B, B has only 100 rows, and we want to fetch the first 1000 rows from A with associated rows from B. Joining in the database will result in 20 * 1000 tuples sent across the network. If the join is done in the app server (first fetching the entire B table into memory), a mere 100 + 1000 rows are sent across the network.

I kinda assumed that a cross-join would be optimised in the client library, so that less data was sent over the network, and then merged, so that the app didn't need to glue the data together. Am I plain wrong, or does it vary depending on the database?

Answer 1

I'm reasonably confident that there is no optimization in the Oracle client libraries for repeating data. If it is, it's below the SQL*Net trace layer which would surprise me.

There are definitely cases where it could make sense to do the join in the app server rather than in the database. But it would be exceedingly rare that you'd have a table structure like this where you would also want to display the result of joining A to B to the user. If you don't want to display the data to the user, you'd just make sure that the results were being processed in the database so that neither A nor B had to be sent over the network.

I have had one or two cases where I needed to get highly denormalized results to a client where it made sense to use the CURSOR function rather than doing a straight join solely in an effort to minimize the amount of data being sent over the network. Of course, that requires that you're selecting a substantial amount of data from table A (DEPT in this case) so that the duplication of data actually becomes problematic.

SQL> ed
Wrote file afiedt.buf

  1  select dname,
  2         cursor( select ename, empno
  3                   from emp e
  4                  where e.deptno=d.deptno) emps
  5*   from dept d
SQL> /

DNAME          EMPS
-------------- --------------------
ACCOUNTING     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
CLARK            7782
KING             7839
MILLER           7934

RESEARCH       CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
smith            7369
JONES            7566
SCOTT            7788
ADAMS            7876
FORD             7902

SALES          CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME           EMPNO
---------- ----------
ALLEN            7499
WARD             7521
MARTIN           7654
BLAKE            7698
TURNER           7844
SM0              7900

6 rows selected.

OPERATIONS     CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Answer 2

There isn't an optimisation in SQL Server libs for repeating data.

Unless I've misunderstood, the logic is very flawed above too

If there are 20 rows in B for each A, a 1000 rows in A implies 20k rows in B. There can't be just 100 rows in B unless there is many-many table "AB" with 20k rows with the containing the mapping.

So to get all information about which 20 of the 100 B rows map to each A row you table AB too. So this would be either:

• 3 result sets of 100, 1000, and 20k rows and a client JOIN
• a single JOINed A-AB-B result set with 20k rows

So "JOIN" in the client does add any value when you examine the data. Not that it isn't a bad idea. If I was retrieving one object from the database than maybe it makes more sense to break it down into separate results sets. For a report type call, I'd flatten it out into one almost always.

In any case, I'd say there is almost no use for a cross join of this magnitude. It's a poor example.

You have to JOIN somewhere, and that's what RDBMS are good at. I'd not like to work with any client code monkey who thinks they can do better.

Edit: after comment

To join in the client requires persistent objects such as DataTables (in .net). If you have one flattened resultset it can be consumed via something lighter like a DataReader. High volume = lot of client resource used.

Answer 3

I would not expect any basic client library to parse, dissect and rearrange the work in a SQL statement like this. The client library would not know what it would need to know to perform this optimisation or gauge if the optimisation was safe any any given instance, as that information is known either by the database (via the structure it knows and stats it keeps that the query planner uses to decide access order and index use) or your code (where you can impart your knowledge of the expected data). The client library just takes your code's commands, passes them onto the database, and marshals the result sets back.

An ORM library may well perform the sort of optimisation you suggest as it maps object based information requests to the query language the backend database uses (i.e. SQL), but you wouldn't be passing SQL queries in that instance.

If you are concerned about the amount of data flowing from the database server to the application server (if they are not directly linked by a nice fast local network then compression in the transport layer would reduce the data size considerably in this instance, but that is not something you would implement in the database or client library, it would be done by what-ever tunnel/vpn software you used to communicate between the app server and the database.

Answer 4

I can pretty certainly say that there's no way that a client library would be capable of handling this level of detail.

As others mention, the library would have to parse the SQL statements. But it goes far, far beyond that. It would have to understand the underlying data structures within the database; it would have to take into account your where clause; it would have to take into account so many things that a client library just isn't made to do.

If you attempted to build this level of optimization into a client library, what you would end up with is a secondary data management system. It would be about half as heavy as the database itself. It's just not the purpose of client libraries.

---

Regarding a cartesian join of 1000 records to another 1000 records, I would completely do this at the application level. If you have a legitimate need to join two tables that would result in an extremely large dataset, it would be very wise to approach that topic carefully with all concepts in mind (particularly network traffic and maintainability of the queries).