At 11:31 AM 09-02-02 +0000, you wrote:

>Hello !!!
>
>I didn't read the other messages but the solution of Lester:
>
>SELECT CU.CUSTOMER
>, ( SELECT DESAGENTE FROM AGENT_TABLE WHERE AGENT_CODE
>= CU.AGENT ) AS AGENT
>, ( SELECT TYPE FROM TYPE_TABLE WHERE TYPE_CODE =
>CU.TYPE ) AS TYPE
>FROM CUSTOMER CU
>
>I think, also is possibly with this other SELECT:

A join and a sub-select don't always produce the same result but, when they do, there isn't really a hard-and-fast rule about which works better on the server side. It's ALWAYS best to test your SQL interactively and watch it in the monitor before you set it in concrete in your app.

Where you are pulling only a scalar value from the linked table, such as a single description, it is generally more economical to use the subselect, especially if the output set is ordered. Apparently FB/IB forms the subselect dataset first (once only) and just "picks off" what it needs for the output set. If the subselect is matching indexed columns, this is very efficient.

If you need multiple columns from the same linked table, a join might be more efficient than a number of sub-selects on a single table. But, where the join is displaying descriptions for a lookup key, it might be more costly on single-row refreshes, since multiple tables will have to be visited for the refresh instead of just a "grab raid" on a single column of a single table.

The other disadvantage of using a join arbitrarily in place of a sub-select is that it complicates the DML operations - you will need to use a KeyRelation, which gives the parser more work to do on inserts, updates and deletes.

>SELECT cu.customer, ag.desagente, tt.type
>FROM customer cu, agent ag,type tt
>WHERE
> ag.agent_code=cu.agent and
> cu.type=tt.type_code

If you are going to form joined datasets for IBO, don't use this syntax. Use the full SQL-92 JOIN clauses:

SELECT cu.customer, ag.desagente, tt.type
FROM customer cu
JOIN agent ag
on cu.agent=ag.agent
JOIN type tt
on cu.type=tt.type_code

Reasons:

1. From an SQL point of view, it's more flexible and easier to read - there's never any confusion about which criteria are for joining and which for searching

2. In IBO if you place JOIN criteria in the WHERE clause, the runtime clause-building won't work unless you define JoinLinks to tell IBO which criteria are for joining. JoinLinks adds extra overhead to all operations on joined sets that the more modern syntax avoids (JoinLinks don't apply to SQL-92 joins).

>I don't know which is better, you have to test it.

Good advice, always test your SELECT statements *and* the DML that would be generated for I/U/D operations.

Additionally, focus hard on making good indexes and clean keys. It will repay you with stunning performance and clutter-free code, on both client and server.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com