Em 16/4/2013 11:09, skander_sp escreveu:

> May be obvious, but til now, i don't see how much unoptimized works the "field in subselect" syntax.
>
> I'm using it in some procedure, just right til now, after check they work in the worse unoptimized mode...
>
> example:
>
> select * from Table1 T1
> where T1.id in (select T2.id
> from Table2 T2
> where T2.Customer=:customer)
>
> of course T2.id is a Primary key and T1.id is a Foreing key
>
> I think they resolve getting the value(s) in the subselect, and then applying to the main where, but the performace analisys show he run all the T1 table from begin to end and then check with the subselect
>
> of course I can solve (in procedure) with a FOR subselect to get every value and check after.... not the nicest way, and pretty ugly and less "human readable", but by far much more quick and optimized.
>
> for select T2.id
> from Table2 T2
> where T2.Customer=:customer
> into :id do
> select * from Table1 T1
> where T1.id=:id
>
> There is some way to get a good result using "field in subselect"? some trick to get an optimized result using this syntax?
>
>

Internally your query is transformed into:

select
*
from
Table1 T1
where
T1.id exists (select
*
from
Table2 T2
where
T2.id = T1.id and
T2.Customer=:customer)


That's why you see a full table scan on T1. Your non correlated sub-query becomes a correlated sub-query after the transformation.


an easy way to achieve what you want is to convert it to a simple JOIN
like this:

select
t1.*
from
Table1 T1 join
Table2 T2 on (T2.id = T1.id)
where
T2.Customer=:customer

if the relationship is not 1:1 you would need to avoid duplicates with
something like:

select
distinct t1.*
from
Table1 T1 join
Table2 T2 on (T2.id = T1.id)
where
T2.Customer=:customer

Some handling for null values should be taken into account if aplicable


if it will be faster or not depends on your data.

see you !