Performance between one-by-one vs. joining tables?

[egsuh]

Which approach would you recomment between two approaches?

Code: MySQL  [Select][+][-]

1. Approach A:
2.  
3. select field2 from table1 where field1 = :keycode into :f2;
4. select field3 from table2 where field2 = :f2 into :f3;
5. // :var1 = some functions on f3
6.  
7.  
8. Approach B:
9.  
10. select field3 from table2 where field2 = (select field2 from table1 where field1=:keycode) into :f3;
11. // :var1 = some functions on f3
12.  

I think if the operation is done within Pascal code, Approach B would have some advantage because it runs query only once, while Approach A need to query twice.

If all operations are done within stored procedure, I prefer Approach A because it is much easier to understand than Approach B.  But if Approach B is much better than Approach A in terms of performance (or memory usage, etc.) than I might write in Approach B.

Welcome advices who have deeper understanding of database and experiences.

[Thaddy]

That highly depends on the SQL engine of the underlying database and you forgot to mention the scenario:
e.g. Oracle is much faster than Firebird in a client-server scenario under heavy load and many nodes.

The SQL query language itself results in a map. How that map is achieved differs greatly between engines.
Your observation to use stored procedures is usually correct in any form of client-server setting, also from a security point of view.
And speed usually comes with a memory trade-off.
 So:
- what is your scenario?
- What database engine are you using
- And what's your budget - if any...

[zamronypj]

Which approach would you recomment between two approaches?

Code: MySQL  [Select][+][-]

1. Approach A:
2.  
3. select field2 from table1 where field1 = :keycode into :f2;
4. select field3 from table2 where field2 = :f2 into :f3;
5. // :var1 = some functions on f3
6.  
7.  
8. Approach B:
9.  
10. select field3 from table2 where field2 = (select field2 from table1 where field1=:keycode) into :f3;
11. // :var1 = some functions on f3
12.  

I think if the operation is done within Pascal code, Approach B would have some advantage because it runs query only once, while Approach A need to query twice.

If all operations are done within stored procedure, I prefer Approach A because it is much easier to understand than Approach B.  But if Approach B is much better than Approach A in terms of performance (or memory usage, etc.) than I might write in Approach B.

Welcome advices who have deeper understanding of database and experiences.

Many RDBMS has command to show how a query is executed (i.e query execution plan). For example MySQl has EXPLAIN. Run it first to understand how performant is your query.

Approach A requires more network roundtrip but they are simple query thus have greater chance to fit in query cache buffer.

Approach B requires less network roundtrip but are more complex. More complex query may not fit in query cache buffer.

Many ORM prefers simple query (for example Laravel Eloquent) because of the tendency that it will fit in query cache.

[marcov]

Many RDBMS has command to show how a query is executed (i.e query execution plan). For example MySQl has EXPLAIN. Run it first to understand how performant is your query.

Sound and best advise. Never assume to much, measure!

Approach A requires more network roundtrip but they are simple query thus have greater chance to fit in query cache buffer.

Approach B requires less network roundtrip but are more complex. More complex query may not fit in query cache buffer.

I assume most SQL query plan engines notice that the inner query is fairly constant. This would mean they are nearly equivalent, but lets the SQL engine allocate an internal anonymous dataset for it (or not even that if field2 is indexed), rather than allocating a named temptable for it.