Is the new(ish) FORMAT function in SQL Server slow?
SQL Server 2012 saw the introduction of the T-SQL FORMAT function, making it easier to present dates nicely (no more remembering CONVERT arguments such as 103!). But is it too slow to be really useful? This blog aims to test this scientifically.

Posted by Andy Brown on 16 January 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Assessing the speed of the FORMAT function in T-SQL

The FORMAT function was a welcome addition in SQL Server 2012, since it allows you to format dates so much more easily.  For example, this SQL:

-- use any custom date format

SELECT FORMAT(

GetDate(),

'dddd dd MMMM yyyy'

) AS 'Beautiful date'

Would give this output (today - worryingly - is Friday 13th January):

Formatted date

Here we've chosen to display the day name, number, month name and year.

 

SQL Server 2016 adds an optional third argument to the FORMAT function, allowing you to return language-specific formatting.

However, this feature comes with a price: it's slow, everyone says.  But how slow?  This blog attempts to answer the question. 

Creating a test table

To test this out, I've generated a table with 5,000,000 rows (although only 3 columns).  First I manually created a table:

Table of test data

The table includes 3 columns: a unique row number (which is an identity column), the row name and a somewhat random date.

 

I then ran the following query to generate test data:

-- get rid of any old data

TRUNCATE TABLE tblRow

-- the number of rows to generate

DECLARE @max int = 5000000

-- various variables used

DECLARE @i int = 1

DECLARE @d int

DECLARE @m int

DECLARE @s varchar(100)

-- keep going till we've created

-- enough rows

WHILE @i <= @max

BEGIN

-- use modulus to generate different days

-- and months (not random, but this

-- won't matter)

SET @d = @i % 5 + 1

SET @m = @i % 7 + 2

-- generate a date in 2017 with this day

-- and month

SET @s = '2017-0' +

CAST(@m AS varchar(1)) + '-0' +

CAST(@d as VARCHAR(1))

-- add this date into our table

INSERT INTO tblRow (RowName,RowDate)

VALUES ('test ' + CAST(@i as varchar(50)),

cast(@s as date))

SET @i = @i + 1

END

SELECT * FROM tblRow

The result is an uninspiring table of dates, the first few of which are as follows:

Rows of dates

Bedtime reading this isn't, but we have 5 million rows containing a date field.

 

The test queries

What I'm interested to know is how much time it takes SQL Server to show dates, using 3 different methods:

  1. without formatting;
  2. using the CONVERT function (the traditional way); and
  3. using the FORMAT function

To people who say that you shouldn't use SQL for formatting, I say: read a different blog!

The SQL used is as follows, with each of the 3 lines of code commented in: 

SELECT

r.RowName,

-- show one (and one only) of the following

-- 1) r.RowDate

-- 2) convert(char(10),r.RowDate,103)

-- 3) format(r.RowDate,'dd/MM/yyyy')

FROM

tblRow AS r

The results compared

So here is how long each query took to run, on a Dell Precision M6800 laptop with this specification, and nothing much else running in the background apart from SQL Server Management Studio:

Laptop spec

The laptop I'm using!

The results?

Method Description Seconds
1 Date only, without formatting 18
2 Using CONVERT function 21
3 Using FORMAT function 46

So the conclusion is that the FORMAT function - at least for this example - takes over twice as long. 

Does this matter?  That depends on how many rows of data you are querying, and how long you mind waiting for the results.  Your call!

This blog has 0 threads Add post