How do duplicated expressions effect the performance of queries?-CodePudding

When I write something like

SELECT Cast(DateDiff(milliseconds, col1, col2) / DateDiff(milliseconds, col3, col4) as int as [Result] 
FROM [sometable]
WHERE Cast(DateDiff(milliseconds, col1, col2) / DateDiff(milliseconds, col3, col4) as int > 4

Is that inefficient and how to do it better?

CodePudding user response：

Is that inefficient?

No, SQL Server will evaluate the expression once and re-use it.

how to do it better?

You can laterally join a re-usable values with a cross apply and a values table constructor:

Select MyValue, OtherCols
from SomeTable
cross apply(values( <my expression> ))v(MyValue)
where MyValue = ?;

CodePudding user response：

Yes, I Mean that is inefficient. you can try following this.

SELECT [Result]
FROM (SELECT Cast(DateDiff(milliseconds, col1, col2) / DateDiff(milliseconds, col3, col4) as int as [Result] FROM [sometable]) as temp
WHERE [Result] > 4