Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Sorry for late comment: where does (@number come from? Is this code in a scalar udf that is called row by row? –  gbn Aug 7 '11 at 15:14
add comment

6 Answers

up vote 6 down vote accepted

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.

share|improve this answer
1  
atleast a simple optimization? i was looking for something more from stackoverflowers ! –  Deeptechtons Jan 25 '11 at 11:49
1  
"here is no elegant string concatention in SQL Server": it really is this simple, sorry –  gbn Jan 25 '11 at 12:26
1  
i go with you Gbn certain things are better done on presentation layer –  Deeptechtons Jan 27 '11 at 6:47
    
@Deeptechtons - Indeed. As the saying goes, "The only winning move is not to play". The best solution is not to do formatting in the database. –  Thomas Mar 3 '11 at 3:40
    
This is no longer true in SQL Server 2012. –  Nick Chammas Nov 16 '11 at 16:59
show 1 more comment

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.

share|improve this answer
    
but isn't this operation too much for meager performance improvements –  Deeptechtons Jan 27 '11 at 6:48
    
I'm not sure what you mean - can you reword your question? Are you saying adding a persisted computed field is too much work? –  Brent Ozar Jan 28 '11 at 22:29
add comment

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:

share|improve this answer
    
seems promising to me, but yes since this is task for UI layer the next project that used same technique did it in the UI layer –  Deeptechtons Nov 17 '11 at 4:46
add comment

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!

share|improve this answer
    
Actually that's not a bad idea, hand off complex conversions to a CLR component, that can be jittered to be much faster. –  jcolebrand Jan 25 '11 at 18:25
    
well that means i need to get the previous inserted code [VI---] back to presentation layer persist it and then do operation on it then insert it back again. which means: select max(thecode) from blahblahblah then do right with 6 chars and then add 0 to make it int and then add 1 to increment it huhh sounds simple but let me try that –  Deeptechtons Jan 27 '11 at 6:44
add comment

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.

share|improve this answer
    
I thought of this too there is no CONVERT style for mm/yyyy. There are ones that contain this but we'd need an extra SUBSTRING or such instead... –  gbn Jan 25 '11 at 13:19
    
@gbn. Aye, that'll the the "1x substring operation" listed in "1x conversion date->string and 1x substring operation". The original is doing 2x date manipulations and 2x int->string conversions, which may or may not be slower overall. –  David Spillett Jan 25 '11 at 21:15
    
i too go with dbn –  Deeptechtons Jan 27 '11 at 6:48
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.