On Tue, 20 Mar 2012 10:46:31 -0000, "nathanelrick" <[email protected]> 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 ...
