Is there any reason this simple SQL query should be so slow?

Question

Hi,

This query takes about a minute to give results:

SELECT MAX(d.docket_id), MAX(cus.docket_id) FROM docket d, Cashup_Sessions cus

Yet this one:

SELECT MAX(d.docket_id) FROM docket d UNION MAX(cus.docket_id) FROM Cashup_Sessions cus

gives its results instantly. I can't see what the first one is doing that would take so much longer - I mean they both simply check the same two lists of numbers for the greatest one and return them. What else could it be doing that I can't see?

I'm using jet SQL on an MS Access database via Java.

Answer 1

the first one is doing a cross join between 2 tables while the second one is not.
that's all there is to it.

Answer 2

The first one uses Cartesian product to form a source data, which means that every row from the first table is paired with each row from the second one. After that, it searches the source to find out the max values from the columns.

The second doesn't join tables. It just find max from the fist table and the max one from the second table and than returns two rows.

Answer 3

The first query makes a cross join between the tables before getting the maximums, that means that each record in one table is joined with every record in the other table.

If you have two tables with 1000 items each, you get a result with 1000000 items to go through to find the maximums.