[RESOLVED] SQL - Basic math issue

[TysonLPrice]

The numbers are what are in the table columns. I cannot figure out why it is returning zero.

SQL Server 2014

select convert(decimal(16,6), (6250 - 3104820) / 3104820) * (53 / 100 )

[OptionBase1]

Can't test it, and not sure how SQL handles it, and not motivated enough to look it up right now myself, but could it be that dividing of integers results in an integer type, so (53/100) = 0?

[TysonLPrice]

Can't test it, and not sure how SQL handles it, and not motivated enough to look it up right now myself, but could it be that dividing of integers results in an integer type, so (53/100) = 0?

I think it might be something to do with default data types but also I am so weak in math. Plug it into you calculator...your will get the right answer.

[OptionBase1]

Has nothing to do with calculator math. In many programming languages, the result of an int divided by an int is an int. And an int has no decimal value, so values are truncated.

So 53 / 100 is not 0.53, it is 0.

Change all values in the calculation so they all have a decimal piece (so 53.0 / 100.0, etc.) and see if that changes the result.

[wqweto]

select convert(decimal(16,6), (6250 - 3104820) / 3104820) returns 0.000000

select convert(decimal(16,6), (6250 - 3104820) / 3104820.0) returns -0.997987

You have to decide what data types you want to use to the intermediate result -- DECIMAL (aka fixed point) or FLOAT (aka floating point).

We are using DECIMAL(24, 12) for all out calculation but sometimes the precision is not enough. Here is how we coerce intermediate data-types

select convert(decimal(16,6), 
        CONVERT(DECIMAL(24, 12), 
        CONVERT(DECIMAL(24, 12), 
        CONVERT(DECIMAL(24, 12), 6250 - 3104820)
            / CONVERT(DECIMAL(24, 12), 3104820))
            * CONVERT(DECIMAL(24, 12), 
                CONVERT(DECIMAL(24, 12), 53)
                / CONVERT(DECIMAL(24, 12), 100))))

It's a mouthful but this allows us to control exactly precision/scale escalation during the intermedate steps in complex calculations.

cheers,
</wqw>

[szlamany]

The numbers are what are in the table columns. I cannot figure out why it is returning zero.

SQL Server 2014

select convert(decimal(16,6), (6250 - 3104820) / 3104820) * (53 / 100 )

If you are doing this deep a level of math, then this formula needs to be in a scalar function where you use properly typed variables. If you were in VB you would most likely not put this type of formula in-line in your code - treat T-SQL the same way.

Drop Function dbo.DoCalc_F
Go
Create Function dbo.DoCalc_F(@F1 decimal(16,6), @F2 decimal(16,6))
Returns decimal(16,6)
as
Begin 
Declare @X1 decimal(16,6)
Declare @X2 decimal(16,6)
Declare @X3 decimal(16,6)

Set @X1=53
Set @X2=100

Set @X3=(@F1 - @F2) / @F2 * (@X1 / @X2)
--decimal(16,6), (6250 - 3104820) / 3104820) * (53 / 100 ) 

Return @X3

End

Select dbo.DoCalc_F(6250,3104820)

---------------------------------------
-0.528933

(1 row affected)

[TysonLPrice]

Thanks all...as stated I needed decimals.

[wqweto]

Don't to this with scalar UDF as these are performance killers for large resultsets. It is only sql2019 that tries to inline scalar UDFs so you *might* not get penalized if such calculations are extracted into UDFs.

On any previous db engine version though expanding and repeating the full calculation inline (however bulk) is the only option to get any decent performance. . . ¯\_(ツ)_/¯

cheers,
</wqw>

[szlamany]

Don't to this with scalar UDF as these are performance killers for large resultsets. It is only sql2019 that tries to inline scalar UDFs so you *might* not get penalized if such calculations are extracted into UDFs.

On any previous db engine version though expanding and repeating the full calculation inline (however bulk) is the only option to get any decent performance. . . ¯\_(ツ)_/¯

cheers,
</wqw>

Been creating enterprise systems for 20 years with all business logic in T-SQL sprocs and udf's and tvf's. I've never seen a problem with speed and my clients have tables with millions of rows (health and education industries).

I very strongly disagree with your assessment of performance killing!

[szlamany]

Truly simple test for speed here. Only 1.5 million rows - both inline math and same select with a UDF.

Adding three INT fields from the table.

Both took 6 seconds.

Speed of access in SQL is nearly all buried in I/O. Everything past the I/O portion of any bottleneck is small potatoes in comparison, imo.

[szlamany]

Here is another comparison. Instead of returning 1.5 million results, I'm doing a MIN().

The in-line math runs instantly - recording zero seconds.

The UDF takes 2 seconds.

On 1.5 million calls, I would not consider "2 seconds" to be "performance killers".

[szlamany]

Here is a set of "scripts" for UDF's at one of my clients sites. I've got 50 of them - doing things as simple as figure your age to building "address lines" from many "address fields".

[TysonLPrice]

Maybe he was referring to something like this. I'm not disagreeing with your test results. Maybe "it just depends".

https://www.red-gate.com/simple-talk...-code-changes/

Why are Scalar UDF Functions inherently slow?

When running a scaler UDF on a database with a compatibility level set to less than 150, they just don’t scale well. By scale, I mean they work fine for a few rows but run slower and slower as the number of rows processed gets larger and larger. Here are some of the reasons why scalar UDF’s don’t work well with large recordsets.

When a T-SQL statement uses a scalar function, the database engine optimizer doesn’t look at the code inside a scalar function to determine its costing. This is because Scalar operators are not costed, whereas relational operators are costed. The optimizer considers scalar functions as a black box that uses minimal resources. Because scalar operations are not costed appropriately, the optimize is notorious for creating very bad plans when scalar functions perform expensive operations.
A Scalar function is evaluated as a batch of statements where each statement is run sequentially one statement after another. Because of this, each statement has its own execution plan and is run in isolation from the other statements in the UDF, and therefore can’t take advantage of cross-statement optimization.
The optimize will not allow queries that use a scalar function to go parallel. Keep in mind, parallelism may not improve all queries, but when a scalar UDF is being used in a query, that query’s execution plan will not go parallel.

[wqweto]

Of course *scalar* UDFs performance problems are documented. Not table-valued UDFs (multi-step or not), not SPs but exactly scalar ones.

We do have UDFs (thousands of them) and have been using these since sql2000 but I would never recommend encapsulating logic is scalar UDFs without first considering performance implications on the particular SQL Server version the solution is targeting.

Recent sql2019 mitigates such performance issues by successfully inlining scalar UDFs if these are not particularly complex (e.g. WHILE loops comes to mind) so this might be ok to use.

With RDBMs I would always prefer to sticking with "cave-age solutions" instead of using "latest shiny slick" ones out of habit -- been biten too many times already.

cheers,
</wqw>

[szlamany]

Maybe he was referring to something like this. I'm not disagreeing with your test results. Maybe "it just depends".

https://www.red-gate.com/simple-talk...-code-changes/

Why are Scalar UDF Functions inherently slow?

When running a scaler UDF on a database with a compatibility level set to less than 150, they just don’t scale well. By scale, I mean they work fine for a few rows but run slower and slower as the number of rows processed gets larger and larger. Here are some of the reasons why scalar UDF’s don’t work well with large recordsets.

When a T-SQL statement uses a scalar function, the database engine optimizer doesn’t look at the code inside a scalar function to determine its costing. This is because Scalar operators are not costed, whereas relational operators are costed. The optimizer considers scalar functions as a black box that uses minimal resources. Because scalar operations are not costed appropriately, the optimize is notorious for creating very bad plans when scalar functions perform expensive operations.
A Scalar function is evaluated as a batch of statements where each statement is run sequentially one statement after another. Because of this, each statement has its own execution plan and is run in isolation from the other statements in the UDF, and therefore can’t take advantage of cross-statement optimization.
The optimize will not allow queries that use a scalar function to go parallel. Keep in mind, parallelism may not improve all queries, but when a scalar UDF is being used in a query, that query’s execution plan will not go parallel.

This exact point from the paragraph above: "bad plans when scalar functions perform expensive operations."

has zero to do with your simple math formula. UDF's were designed for exactly the purpose of your simple formula. When we rolled our own DB's on VAX/VMS systems in the 80's we had "equation" fields - something I'm really familiar with from an actual backend database programmer world.

Everything in T-SQL has a purpose. Used properly they shine. Used improperly they clunk.

Blanket statements about bad performance need to be challenged and clarified in order to keep this forum clean. That was my intent with these posts - clarifying the "bad performance" characterization so future readers on the forum get full info.

[GaryMazzone]

In 2 seconds can be bad..... I have to return results in less that 2 seconds with internal time (time spent in our DB) should be under 500 ms. We are in the Credit card processing area

[szlamany]

In 2 seconds can be bad..... I have to return results in less that 2 seconds with internal time (time spent in our DB) should be under 500 ms. We are in the Credit card processing area

I ran a query with ONLY the UDF - against 1.5 million rows. That took 2 seconds.

2 seconds / 1.5 million is not a lot of "clock time" per row.

Normally that simple math UDF would be part of a more complex query with lots of fields. All that other "sql" will make the UDF insignificant. I can't believe we are having this discussion at this point, lol!

.0000013 seconds per row...

[wqweto]

I ran a query with ONLY the UDF - against 1.5 million rows. That took 2 seconds.

2 seconds / 1.5 million is not a lot of "clock time" per row.

I've seen bad performing innocuous looking UDFs e.g. in the 30 seconds overhead per mil rows. This one is clearly not a suspect but dealing with strings (VARCHARs) of any kind is probably worst for the engine's memory allocator.

Why would you want to incur any overhead at all? This would escalate quickly on a busy server or with complex queries (based on views calling UDFs or with many JOINs)

For scalar UDFs the age old workaround is to use inline TVFs instead so SELECT dbo.MyScalarUdf(@Param1, @Param2, @Param3) As Col1 is replaced by SELECT (SELECT Value FROM dbo.MyInlineTVF(@Param1, @Param2, @Param3)) As Col1 so that the calculation in the body of the function is really inlined because inline TVF are like views with parameters for the query optimizer i.e. always expanded before optimizing the final query.

cheers,
</wqw>

[wqweto]

Btw, another thing just came up to my mind: Do you know that queries with scalar UDFs cannot go for parallel execution plans?

This might hurt performance a lot if the query is deemed fit for paralel execution otherwise.

[szlamany]

Btw, another thing just came up to my mind: Do you know that queries with scalar UDFs cannot go for parallel execution plans?

This might hurt performance a lot if the query is deemed fit for paralel execution otherwise.

I write complex business applications that have all business logic in SPROCS, UDF's and TVF's. We calculate payrolls in a SPROC, schedule students into high school classes in a SPROC, calculate your pension in a SPROC.

I've done this since I first arrived into the MS SQL world in 2001. This allows me to launch major functionality changes without releasing any "executable" code.

I've always created 4GL's - even since my PDP and VAX days - and the last two (one in VB6 and now the latest in jQuery/JS/Ajax/.Net backend) work perfectly with business logic in T-SQL. Basically all my clients run the exact same "business agnostic" front end along with an equally "business agnostic" VB.Net backend running in IIS.

I've encountered every kind of bottleneck you could imagine! And worked around them. When I started in 2001, the first book I bought was INSIDE MS SQL SERVER 2000 by Kalen Delaney. Having come from a PDP and VAX past we always grew our own ISAM database systems. I digested as many facts as I could, was able to apply that to my past experiences, and have had a hugely successful time working in MS SQL.

That's why I'm the MS SQL PowerPoster, lol!

At any rate - I consider things that happen once the data arrives in memory and is being packaged up for returning the result set to be of the least concern.

I balance things against on going "maintenance" and "future" enhancements...having functions encapsulate logic like this just makes too much sense to me.

If I'm going to have half a dozen tax collectors in PA running the same application, then being able to change UDF's for customization for each client, really helps me out.

Not getting "parallel execution plans" doesn't usually affect my ROI.

[wqweto]

I became Microsoft Certified Trainer in 1998 and the first Microsoft Official Course on SQL Server I lectured was about version 6.5 — yikes! This is before the complete rewrite version 7 was and two version before oldest version 2000 used nowadays.

I dropped teaching at version 2000 and have been producing business applications with MSSQL on a daily basis in the past 20+ years. I’m simultaneously dog-fooding our apps and have been responsible for daily devops on the fleet of DB servers for all our clients in the meantime incl. measuring, planning, executing and A/B testing every possible performance optimization.

I’ve had great success with implementing SQL Server solutions so far.

[szlamany]

I’ve had great success with implementing SQL Server solutions so far.

That is great to hear!

And now we can both walk away realizing that there are several ways to successfully utilize the MS SQL tool that we have all come to love!