Optimize this SQL query for performance

Question

I'm trying to optimize the following statement:

'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR)
+'/00000' +CAST(@number+1 AS VARCHAR)

The statement produces a value like VI1/2011/000002 if the @number parameter is 1.

I would like to optimize this in terms of removing redundant cast statements and providing an efficient way to concatenate the strings and integers.

Answer 1

No shortcut at all, there is no elegant string concatention in SQL Server

You are mixing varchar and nvarchar though: datatype precedence means the entire expression will be nvarchar.

Answer 2

If you find yourself frequently performing this conversion as data gets selected out, try using a persisted calculated field instead. It gets added to the table, and SQL Server automatically calculates it whenever the data is inserted or updated. You pay the calculation penalty once - just once - and then it's less CPU whenever the data is selected back out. You can even put an index on it if you find yourself filtering queries with that field.

Answer 3

SQL Server 2012 introduces a new function that seems perfectly suited to your needs: CONCAT().

Here's some example code:

DECLARE @number INT             = 1;
DECLARE @now    DATETIME2(3)    = GETDATE();

SELECT 
    CONCAT(
          'VI'
        , DATEPART(month, @now)
        , '/'
        , DATEPART(year, @now)
        , '/00000'
        , (@number + 1)
    )   AS result
;

That said, I do recommend as others have to do presentation work in your presentation layer and not in the database. In those cases where it is just easier to throw it into your existing SQL query, however, I believe CONCAT() will serve you well.

That covers the programmability aspect. I don't know how well this would perform, though I would bet certainly no worse than your existing solution.

For more examples, take a look at:

• Pinal Dave's intro
• Ana @ Beyond Relational's intro

Answer 4

Can you not get this as a parameter and pass it into the SQL proc from the application layer such as .net or java or php... ?

It would be much simpler not to do this at all on the SQL server!

Answer 5

As gbn suggests (this answer started as a comment to his post, but grew ttoo long to be practical in that area) the only obvious simple optimisation it to make that VARCHAR an NVARCHAR and save one type conversion per invocation. The way you have it there will be an explicit conversion to VARCHAR followed by an implicit conversion to NVARCHAR. Or make the NVARCHARs VARCHARs if you want a VARCHAR out at the end anyway.

You could perhaps change the CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) to instead CONVERT() to a string dat format that includes mm/yyyy and use SUBSTRING to pull that part out. That way you have:

• 1x conversion date->string
• 1x sub-string operation

instead of

• 2x operations pulling integer parts out of a date
• 2x type conversions int->string
• some extra string concatenation (though as there is other concatenation going on anyway this will make little difference as it'll do it all at once)

per invocation.

But really this is not going to save you much, if anything. Even if it did save anything in terms of CPU load or wall-clock time I expect it would very very small even over a large result set and/or in a long loop. And the format would not be quite the same: your current format includes m/yy (no leading zero for months lower than 10) and this would yield mm/yy.

Answer 6

Agree with gbn on mixing nvarchar with varchar, an alternative way of making the sql easier to read (doesn't make it any faster/slower to run), could be:

/* opt 1 */
select 'VI'+ SUBSTRING(CONVERT(varchar, getdate(), 103), 4, 7) 
        + '/00000' + CONVERT(varchar, @number+1)

vs

/* opt 2 */
select 'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR)
+'/00000' +CAST(@number+1 AS VARCHAR)

---

IMPORTANT NOTE: When month is < 10 different result produced:

opt 1 produces: VI1/2011/000002
opt 2 produces: VI01/2011/000002