CODEWITHSUNDEEP
sql-serverfunctiont-sql
ImmortalStrawberry
ImmortalStrawberry

Reputation: 6101

Better performance from CASE statement or Function in T-SQL

I have a set of tables that have the following structure repeated multiple times:

Sign|Value
    |1000
   -|1000

So there is a "sign" column followed by the "value" column...and both values are varchar(..). I am building tables from this base data and want to convert to a decimal using sign.

At the moment I have been doing the following:

CASE
    WHEN LTRIM(RTRIM(s.PreviousPointBalanceSign)) = '-'
    THEN -1 * CAST(s.PreviousPointBalance AS decimal(14,2))
    ELSE  CAST(s.PreviousPointBalance AS decimal(14,2))
END AS PreviousPointBalance

But I would like to know if using a function to achieve this would give me better performance?

Upvotes: 3

Views: 4447

Answers (2)

Stephan
Stephan

Reputation: 6018

Obviously the best choice would be to convert your two columns into one, but since that's not an option. I think your stuck. If you could alter your table and make [sign] char(1), you'll have 2 bytes less per row. Which is you have 25 million rows * 2 bytes = 50MB. So narrowing that should help at least a little bit.

As far as a function, I don't know if there is a faster way to do it. There is a simpler way though like so:

SELECT CAST(CONCAT(RTRIM([sign]),value) AS DECIMAL(14,2)) AS PreviousPointBalance
FROM yourTable

You only need RTRIM() because SQL Server doesn't care about spaces around the varchar to be casted to decimal. If you were to alter your table to CHAR(1), you wouldn't even have to worry about RTRIM()

SELECT CAST('   -99  ' AS DECIMAL(14,2)) --works
SELECT CAST('-   99  ' AS DECIMAL(14,2)) --fails

Upvotes: 1

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5757

What kind of function are you planning to use? Basically functions does not help with the performance. They simplify the code, they give you ability to re-use the code and so on. Plain SQL will always be better or the same. In your case the best solution would be to store the data in the proper way, however if it is not possible then try to do the same thing in different ways. For example:

    1) CASE WHEN Sign like '%-%';
    2) CASE WHEN LEN(Sign) > 0.
3) You can also avoid CASE at all: SELECT CAST(Value AS Decimal(14,2)) * ((-1 + (LEN(Sign)^1) * 2))

You can also use calculated persisted column to calculate the value when it is inserted.

UPD:

After small tests, optimizer shown absolutely the same plan for all of the ways:

CREATE TABLE #test
(
        value VARCHAR(3),
    [sign] VARCHAR(3)

);

DECLARE @a INT = 0;
WHILE (@a < 1000)
BEGIN
INSERT INTO #test VALUES (CAST(@a AS VARCHAR(100)), CASE WHEN @a%2 =0 THEN '-' ELSE '' END )
SET @a=@a+1;

END


SELECT 
CASE
    WHEN LTRIM(RTRIM(s.[sign])) = '-'
    THEN -1 * CAST(s.value AS decimal(14,2))
    ELSE  CAST(s.value AS decimal(14,2))
END AS PreviousPointBalance
FROM #test s

SELECT 
CASE
    WHEN s.[sign] LIKE '%-%'
    THEN -1 * CAST(s.value AS decimal(14,2))
    ELSE  CAST(s.value AS decimal(14,2))
END AS PreviousPointBalance
FROM #test s

SELECT 
CASE
    WHEN  LEN(s.sign) >0
    THEN -1 * CAST(s.value AS decimal(14,2))
    ELSE  CAST(s.value AS decimal(14,2))
END AS PreviousPointBalance
FROM #test s

SELECT  CAST(Value AS Decimal(14,2)) * ((-1 + (LEN(Sign)^1) * 2)) AS PreviousPointBalance
FROM #test s

Upvotes: 1

Related Questions