it's a bad new that i have no way :(
when i try the CTE, the prepare seam to grow ("seam" i just do now some tests).
also CTE are a little hard to use in my situation ...
> Having said that, a year or so ago, Ann Harrison wrote that repeating a
> subquery doesn't add much to the execution speed, so:
>
> SELECT T1.PK, (SELECT T2.F1 FROM T2 WHERE T1.PK = T2.PK) AS A1,
> CASE WHEN (SELECT T2.F1 FROM T2 WHERE T1.PK = T2.PK) = T1.PK THEN 1
> else 0 end
> FROM T1
>
> might not take much more time than when only using the subquery once (though
> test it, your question is probably different to the one she responded to).
i have just done some test now :
select
(select xxx from yyy where yyy.ID=MyTable.ID) as field1
from Mytable
average time taken by select : 10ms
select
(select xxx from yyy where yyy.ID=MyTable.ID) as field1,
(select xxx from yyy where yyy.ID=MyTable.ID) as field2,
(select xxx from yyy where yyy.ID=MyTable.ID) as field3,
(select xxx from yyy where yyy.ID=MyTable.ID) as field4,
(select xxx from yyy where yyy.ID=MyTable.ID) as field5,
(select xxx from yyy where yyy.ID=MyTable.ID) as field6
from Mytable
average time taken by select : 30ms (3x more slower)
select
(select xxx from yyy where yyy.ID=MyTable.ID) as field1,
'aaa' as field2,
'bbb' as field3,
'ccc' as field4,
'ddd' as field5,
'eee' as field6
from Mytable
average time taken by select : 12ms
so it's mean than repeating the same subquery add a little to the execution ...
