Geek Answers Handbook

Avoid calling scalar functions in UPDATE statements simultaneously

Is it possible to change the following UPDATE so that the scalar function is called only once, and not twice?

 UPDATE a SET SomeField = b.SomeField FROM TableA AS a JOIN TableB b ON b.Link = a.Link WHERE b.CostMin <= @Cost * dbo.OneScalarFunction(@CurrencyFrom, b.Currency) AND b.CostMax >= @Cost * dbo.OneScalarFunction(@CurrencyFrom, b.Currency)

PS Using the BETWEEN operator does not help - SQL Server calls the scalar function twice.

+6
source share
3 answers

This is often significantly more productive, but requires you to change the scalar function to an inline function oriented to the table.

 UPDATE a SET SomeField = b.SomeField FROM TableA AS a CROSS APPLY dbo.oneInlineTableValuedFunction(@CurrencyFrom, e.Currency) AS ITVF INNER JOIN TableB b ON b.Link = a.Link WHERE b.CostMin <= @Cost * ITVF.exchangeRate AND b.CostMax >= @Cost * ITVF.exchangeRate

Although table functions return tables, you can choose to reconfigure only one row with one field. Then you use it effectively as a scalar function. But you get all the benefits of how SQL Server can optimize the query above ...
- If TVF is Inline (and not multi-expression)
- TVF expanded into request
- Result - performance significantly exceeding scalar functions


An example of an embedded table. Values:

 CREATE FUNCTION dbo.oneInlineTableValuedFunction ( @currencyFrom VARCHAR(32), @currencyTo VARCHAR(32) ) RETURNS TABLE AS RETURN ( SELECT exchangeRate FROM dbo.someTable WHERE currencyFrom = @currencyFrom AND currencyTo = @currencyTo )

Intentionally trivial

One example of reporting this: scalar-functions-inlining-and-performance

If you take a look at the net for INLINE CROSS APPLY SCALAR FUNCTION PERFORMANCE , I'm sure you will get a lot more.

+5
source

Try to perform BETWEEN .

 WHERE functionCall(..) BETWEEN minValue AND maxValue

http://www.w3schools.com/sql/sql_between.asp

+5
source

You can try using BETWEEN (as shown below), although you will need to test it, as I have a suspicious suspicion that the database might crash it into execution like> = and <= anyway ..

 UPDATE a SET SomeField = b.SomeField FROM TableA AS a JOIN TableB b ON b.Link = a.Link WHERE @Cost * dbo.OneScalarFunction(@CurrencyFrom, e.Currency) BETWEEN b.CostMin AND b.CostMax
+3
source

More articles:


All Articles