JOIN performance vs. select in a loop

Question

I am trying to explain a performance issue that puzzles me...

I have 2 tables, A and B.

Table A defines the objects:

+----+--------------+
+ ID + other_things +
+----+--------------+
+ 1  + ~~~~~~~~~~~~ +
+ 2  + ~~~~~~~~~~~~ +
+ 3  + ~~~~~~~~~~~~ +
+----+--------------+

There is 575 rows in this table.

Table B defines some properties for said objects.

+----+-------------+-------------+
+ ID + prop_type   +  prop_value +
+----+-------------+-------------+
+ 1  +    prop1    +   foo       +
+----+-------------+-------------+
+ 1  +    prop2    +   toto      +
+----+-------------+-------------+
+ 3  +    prop2    +  lorem      +
+----+-------------+-------------+  

There is 20254 rows in this table.

The aim is to get all of the 'prop2' values for the items in table A.

Some users around here have been kind enough to help me set up the 'good' solution (see q. 41331902):

SELECT A.ID, B.prop_value
FROM A LEFT JOIN
     B
     ON A.ID = B.ID AND B.prop_type = 'prop2';

This query executes in about 20 seconds.

However, an other version is currently used, that I was trying to improve at the beginning:

FOR (
SELECT ID as id_a FROM A
)
DO
prop = (select prop_value from B where prop_type = 'prop2' and id = id_a);
write id_a || '   ' || prop;
END

This gives the same results, but the query is completed in 1.5 seconds...

It is my understanding from what I read on the web than the JOIN should be way better that a loop, but the actual results say otherwise...

I have tried to change the order of tables (ie. big join small), but that only made things worse (up to 1 minute).

Could you help me understand what happens here?

Side note: I can't get an execution plan, since the DB engine does not allow it (AspenTech's SQLPlus)

Thanks a lot for your help

Answer 1

If you have this query:

SELECT A.ID, B.prop_value
FROM A LEFT JOIN
     B
     ON A.ID = B.ID AND B.prop_type = 'prop2';

And you want to speed it up, create an index on B:

CREATE INDEX idx_b_id_type_value ON B(id, prop_type, prop_value);

This should greatly increase the performance.

Your two-query version would be faster if B had an index where prop_type is the first key in the index. I should add: I don't have experience with Aspen SQL. Another possibility is that it just has a lousy optimizer.