Re: Issues with Sub-selects and performance on Win 2003

[A. Weh]

These are fairly simple subselects that should work quite fast.
To get good performance you need the following:

1. The draftid must be the same datatype in any of the used tables and must have the same lenght (e.g. in CHAR columns).
2. There must be an index on this column in any of the used tables

If this is the case the optimizer will use the indices to speed the query (regardless if it is a subselect or a join)

Especially condition 1 is more restrictive than in other database systems, so this might be the reason for the bottleneck. You find more details on this in the dokumentation.
BTW, the names of the columns must be identical too (which is the case in your example, but someone might struggle about ist).

Using the EXPLAIN statement will reveale in detail what the optimizer does.

Good luck !

A. Weh