Reuse field values in WHERE clause

Question

How do I optimize the performance of the following T-SQL so heavyFunctionCall function will get called just once.

Looking for the fastest option among table variables, temp tables, CTEsor something else?

SQL:

select dbo.heavyFunctionCall(a, b, c) 
from T 
where dbo.heavyFunctionCall(a, b, c) > 10

Have you tried looking at the query plan for the various options you describe? — Rhys Jones, Feb 24 '15 at 11:47
I have not. My knowledge of TSQL is superficial. I thought that I can naturally use fields from select in where which isn't the case. — user1514042, Feb 24 '15 at 11:48

score 3 · Accepted Answer · answered Feb 24 '15 at 12:00

3

Doing this would only run your function once on every row instead of twice:

SELECT * 
FROM (
  SELECT dbo.heavyFunctionCall(a, b, c) AS x
  FROM T) a
WHERE x > 10

answered Feb 24 '15 at 12:00

Dave Sexton

10,768
3
42
56

score 1 · Answer 2 · answered Feb 24 '15 at 12:15

1

declare proc tst (@x int)  -- set @x whatever you want.
                           -- the execution plan will be the same.
as 
begin
SELECT * 
FROM (
  SELECT dbo.heavyFunctionCall(a, b, c) AS result
  FROM T) resultx
WHERE result > @x
end

answered Feb 24 '15 at 12:15

Dudi Konfino

1,126
2
13
24

score 1 · Answer 3 · answered Feb 24 '15 at 14:32

1

maybe this:

select hFC.result
from T 
cross apply ( select dbo.heavyFunctionCall(T.a, T.b, T.c) result ) hFC
where hFC.result > 10

answered Feb 24 '15 at 14:32

A ツ

1,267
2
9
14

Reuse field values in WHERE clause

3 Answers3