Looking for smart algorithm or function?

Transact-SQL
1

I have a nice little challenge: I need to apply an algorithm to a huge amount of numbers in a complex simulation. Therefore the algoritm or maybe function should be really fast.

Here goes the rule:
Operation times should be increased or reduced by a certain percentage. The result must be the closest multiple of 0.25 minutes (15 seconds)
Examples:
4 min, 90%: 4 x 9/10 = 3.6 -> 3.5 min
3 min, 90%: 3 x 9/10 = 2.7 -> 2.75 min

This is what I came up with:

declare @percent int = 59
declare @real real = 15.64

select CASE WHEN CAST(@real*@percent/100.0*4 as int)*0.25 + 0.175 >= @real*@percent/100.0 THEN CAST(@real*@percent/100.0*4 as int)*0.25 ELSE CAST(@real*@percent/100.0*4 as int)*0.25 + 0.25 END

Anybody has a better idea? Does the performance of my simulation change if I define this as a function and call it from the query?

Martin

2

I would use:

SELECT ROUND(((@real * (@percent/100.0))/0.25),0)*0.25;

3

This expression seems to work as you intend:

select round(@real*@percent*4,-2)/400.0

I've tested it with a certain range of minutes and percentages, it seems consistent, but you'd need to evaluate it on all your data. It's a variation on @RogierPronk 's version, but minimizing division and floating point operations should be quicker.

I've also tested it with a mix of float and int for all data types in the parameters, but if you have float or real data you may encounter rounding or loss of precision issues as they are approximate types. If you mix different types you may also have issues as the implicit conversion to a uniform type may also lose precision.

Regarding whether to put this into a function, you will incur a performance penalty if you write this (or any other expression) as a scalar user-defined function (UDF) in SQL Server. If you write is as a single-statement table-valued function (TVF) and use it via CROSS APPLY, then the query optimizer should be able to use it effectively.

As the expression I posted is really simple, I'd recommend against putting it into a UDF. The scaffolding for the function will be longer than the actual code.

4

To answer the question about making it a function - it depends on the type of function. Using an iTVF (inline table-valued function) with CROSS APPLY will result in an execution plan that should be the same as embedding the calculation in the query.

For something like this - I would probably avoid the overhead of a function call and just use CROSS APPLY with VALUES:

SELECT ...
  FROM {your table}       t
 CROSS APPLY (VALUES (round(((t.real * (t.percent / 100.0)) / 0.25), 0) * 0.25)) As e(result)
 WHERE ...