Performance of CAST vs CONVERT

2019-08-28 · by Hannah Vernon · in performance

This post is a follow-up to my prior post inspecting the performance of PARSE vs CAST & CONVERT, where we see that PARSE is an order of magnitude slower than CONVERT. In this post, we’ll check if there is a similar difference between using CAST or CONVERT. But just to be clear, CONVERT offers a lot more functionality than CAST; this post will not help you decide which of these functions to use for a specific use-case – I leave that to the reader to decide for themselves.

Country Kermis, by David Teniers, the Younger. Fun performance, but not much cast vs convert in this image.

Minimal, Complete, and Verifiable Example

The code below is borrowed from the post on PARSE vs CAST & CONVERT, except here we’re inserting 1,000,000 rows into the dbo.ParseTest table since CAST and CONVERT are so much faster than PARSE:

USE tempdb;

DROP TABLE IF EXISTS dbo.ParseTest;
DROP TABLE IF EXISTS dbo.ParseTestOutput1;
DROP TABLE IF EXISTS dbo.ParseTestOutput2;
GO
 
CREATE TABLE dbo.ParseTest
(
    d varchar(23) NOT NULL
);
 
INSERT INTO dbo.ParseTest(d)
SELECT TOP(1000000) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2019-08-20 00:00:00')
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;
 
CREATE TABLE dbo.ParseTestOutput1
(
    d datetime NOT NULL
);
 
CREATE TABLE dbo.ParseTestOutput2
(
    d datetime NOT NULL
);
 
SELECT *
FROM dbo.ParseTest;
 
GO

USE tempdb;

DROP TABLE IF EXISTS dbo.ParseTest;

DROP TABLE IF EXISTS dbo.ParseTestOutput1;

DROP TABLE IF EXISTS dbo.ParseTestOutput2;

CREATE TABLE dbo.ParseTest

(

d varchar(23) NOT NULL

);

INSERT INTO dbo.ParseTest(d)

SELECT TOP(1000000) DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2019-08-20 00:00:00')

FROM sys.syscolumns sc1

CROSS JOIN sys.syscolumns sc2;

CREATE TABLE dbo.ParseTestOutput1

(

d datetime NOT NULL

);

CREATE TABLE dbo.ParseTestOutput2

(

d datetime NOT NULL

);

SELECT *

FROM dbo.ParseTest;

The last statement simply pulls data from the dbo.ParseTest table into memory; if your server is low on memory you may want to reduce the number of rows for your testing. On my test server, the code above took approximately 20 seconds to insert the rows, return them to SSMS, and render them to the screen.

The code below compares the performance of CAST to CONVERT:

SET STATISTICS IO, TIME ON;
GO
 
INSERT INTO dbo.ParseTestOutput1 (d)
SELECT CAST(pt.d AS datetime)
FROM dbo.ParseTest pt;
GO
 
INSERT INTO dbo.ParseTestOutput2 (d)
SELECT CONVERT(datetime, pt.d)
FROM dbo.ParseTest pt;
GO

SET STATISTICS IO, TIME OFF;

SET STATISTICS IO, TIME ON;

INSERT INTO dbo.ParseTestOutput1 (d)

SELECT CAST(pt.d AS datetime)

FROM dbo.ParseTest pt;

INSERT INTO dbo.ParseTestOutput2 (d)

SELECT CONVERT(datetime, pt.d)

FROM dbo.ParseTest pt;

SET STATISTICS IO, TIME OFF;

Notice I’m not specifying a specific type of output for the CONVERT function; this is simply to get as close to an apples-to-apples comparison as possible. The Messages tab in SSMS shows the following results for our little test:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'ParseTestOutput1'. Scan count 0, logical reads 1002100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParseTest'. Scan count 1, logical reads 3953, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 5312 ms,  elapsed time = 5336 ms.

(1000000 rows affected)


SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'ParseTestOutput2'. Scan count 0, logical reads 1002100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParseTest'. Scan count 1, logical reads 3953, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 5516 ms,  elapsed time = 5509 ms.

(1000000 rows affected)

As you can see from the output above, CAST is very slightly quicker. If you re-run this test code many times, you’ll see results that vary slightly. Below, I show the CPU and Elapsed times for 10 runs of the test code above. I’ve broken out the times by function to make it easier to compare the two:

CAST:

   CPU time = 5844 ms,  elapsed time = 5867 ms.
   CPU time = 6172 ms,  elapsed time = 6203 ms.
   CPU time = 5906 ms,  elapsed time = 5949 ms.
   CPU time = 5813 ms,  elapsed time = 5842 ms.
   CPU time = 6140 ms,  elapsed time = 6206 ms.
   CPU time = 6156 ms,  elapsed time = 6187 ms.
   CPU time = 6344 ms,  elapsed time = 6373 ms.
   CPU time = 5750 ms,  elapsed time = 5876 ms.
   CPU time = 6265 ms,  elapsed time = 6299 ms.
   CPU time = 5875 ms,  elapsed time = 5891 ms.

CONVERT:

   CPU time = 5953 ms,  elapsed time = 5961 ms.
   CPU time = 6188 ms,  elapsed time = 6263 ms.
   CPU time = 5656 ms,  elapsed time = 5787 ms.
   CPU time = 6406 ms,  elapsed time = 6525 ms.
   CPU time = 5844 ms,  elapsed time = 5854 ms.
   CPU time = 6407 ms,  elapsed time = 6482 ms.
   CPU time = 6078 ms,  elapsed time = 6182 ms.
   CPU time = 5828 ms,  elapsed time = 5859 ms.
   CPU time = 6172 ms,  elapsed time = 6240 ms.
   CPU time = 5969 ms,  elapsed time = 6014 ms.

Average CPU time for CAST is 6026.5 milliseconds. Average CPU time for CONVERT is 6050.1 milliseconds. That’s less than 1% difference between the two.

Summary

It’s safe to assume the performance differences between CAST and CONVERT are negligible, for the case presented above, where we’re converting a character-based date value into a datetime value. In future, I’ll check out using CAST and CONVERT when converting datetime values into various forms of character-based date and time values.

Thanks for reading this post – if you like it, hit the “subscribe” button to get on our mailing list. That way, you’ll be the first to know when we release new blog posts!

Check out the rest of our posts on performance.

Tags: cast, convert

Performance of CAST vs CONVERT

Minimal, Complete, and Verifiable Example

Summary

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta