On Tue, 20 Mar 2012 10:46:31 -0000, "nathanelrick" <nathanelrick@...>
wrote:

> 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 ...

Then do:

SELECT

>> 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 ...