Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions


By:   |   Last Updated: 2013-08-21   |   Comments (8)   |   Related Tips: More > Functions - System

Problem

With every new version of SQL Server comes new and hopefully improved functionality. When SQL Server introduces a new function that sometimes overlaps or replaces the functionality of an already existing function, I am usually curious as to which performs better. This tip will compare the new PARSE function available in SQL Server 2012 with the good old CAST and CONVERT functions. It will also compare the new TRY_PARSE, TRY_CAST and TRY_CONVERT functions which are similar to the previous functions the only difference being they return NULL if the conversion fails rather than an error.  Check out this tip to learn more.

Solution

For those of you that aren't familiar with these new functions check out the following tip which gives a good overview of them:

Since the new parse function deals only with converting data from string to either numeric or date types, we'll just focus on the following three test cases:

  • VARCHAR -> INTEGER
  • VARCHAR -> FLOAT
  • VARCHAR -> DATETIME2

Sample table setup

After using the following table script you can copy some real data from an existing table in your environment into this table or generate some random test data using other tools.

-- Table creation logic
CREATE TABLE [dbo].[TestTable](
 [ColumnID] [int] NOT NULL,
 [IntegerString] [varchar](128) NULL,
 [FloatString] [varchar](128) NULL,
 [DateString] [varchar](50) NULL
) ON [PRIMARY]
GO

Once you've completed loading some random test data or using data from one of your existing environments, querying the table should result in data similar to the following:

Sample Data

Test script

Now that we have a table with some data loaded into it we can use the following script to test out each of the functions on each of the data types we've described above.

select PARSE(IntegerString AS int) from dbo.TestTable;
select CAST(IntegerString AS int) from dbo.TestTable;
select CONVERT(int,IntegerString) from dbo.TestTable;

select PARSE(FloatString AS float) from dbo.TestTable;
select CAST(FloatString AS float) from dbo.TestTable;
select CONVERT(float,FloatString) from dbo.TestTable;

select PARSE(DateString AS datetime2) from dbo.TestTable;
select CAST(DateString AS datetime2) from dbo.TestTable;
select CONVERT(datetime2,DateString) from dbo.TestTable;

select TRY_PARSE(IntegerString AS int) from dbo.TestTable;
select TRY_CAST(IntegerString AS int) from dbo.TestTable;
select TRY_CONVERT(int,IntegerString) from dbo.TestTable;

select TRY_PARSE(FloatString AS float) from dbo.TestTable;
select TRY_CAST(FloatString AS float) from dbo.TestTable;
select TRY_CONVERT(float,FloatString) from dbo.TestTable;

select TRY_PARSE(DateString AS datetime2) from dbo.TestTable;
select TRY_CAST(DateString AS datetime2) from dbo.TestTable;
select TRY_CONVERT(datetime2,DateString) from dbo.TestTable;

Gathering Results

After running the above script a bunch of times (I ran it 10 times so any anomalies are minimized), we can look at the performance of each function. In most cases I use Profiler to capture this data, but this time I thought I'd use the sys.dm_exec_query_stats DMV to gather this information. Given this is a conversion function I am mainly concerned with the amount of CPU consumed while it converts the data so we'll look at the total_worker_time column of the sys.dm_exec_query_stats DMV as well as the total_elapsed_time. We can use the following script to capture these two statistics.

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(qt.TEXT)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1) AS Query,
  qs.Execution_count,
  ROUND(CAST(qs.total_worker_time AS float)/qs.execution_count,0) AS Avg_worker_time,
  ROUND(CAST(qs.total_elapsed_time AS float)/qs.execution_count,0) AS Avg_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.TEXT like '%TestTable%'

Now that we've gathered the statistics let's take a look at the results.

Query Execution_count Avg_worker_time Avg_elapsed_time

PARSE
(IntegerString AS int)

10

11914

12012

CAST
(IntegerString AS int)

10

0

0

CONVERT
(int,IntegerString)

10

0

0

 

PARSE
(FloatString AS float)

10

17871

39746

CAST
(FloatString AS float)

10

0

0

CONVERT
(float,FloatString)

10

293

293

 

PARSE
(DateString AS datetime2)

10

30176

104199

CAST
(DateString AS datetime2)

10

253125

271289

CONVERT
(datetime2,DateString)

10

0

0

 

TRY_PARSE
(IntegerString AS int)

10

8984

67480

TRY_CAST
(IntegerString AS int)

10

73340

140527

TRY_CONVERT
(int,IntegerString)

10

195

195

 

TRY_PARSE
(FloatString AS float)

10

8984

46875

TRY_CAST
(FloatString AS float)

10

63672

113867

TRY_CONVERT
(float,FloatString)

10

0

0

 

TRY_PARSE
(DateString AS datetime2)

10

187695

268750

TRY_CAST
(DateString AS datetime2)

10

488

488

TRY_CONVERT
(datetime2,DateString)

10

201562

231641

Results Analysis

Looking at the results above, we can see that in all cases either the CAST or CONVERT function outperforms the new PARSE function. The most interesting thing to note though is that there is no consistent pattern as to which function performs the best. In some cases CAST performs better than PARSE, which performs better than CONVERT. In other cases, CONVERT performs better than CAST, which performs better than PARSE. I guess the thing to take away from this is that as always it's best to perform your own testing, in your own environment, on your own data, to see which yields the best performance as there are many factors which could impact performance.

Larger Dataset Test

Let's also run this test on a couple of larger datasets to see if this factors into the results at all.  For this test, let's focus on the PARSE, CAST and CONVERT functions for the integer data type. We'll run the same queries as above on a table with 100,000 records and with 200,000 records. Here are the queries as a point of reference.

select PARSE(IntegerString AS int) from dbo.TestTable;
select CAST(IntegerString AS int) from dbo.TestTable;
select CONVERT(int,IntegerString) from dbo.TestTable;

Large dataset Results Analysis

100,000 record dataset
Query Execution_count Avg_worker_time Avg_elapsed_time

PARSE
(IntegerString AS int)

10

9814778

10896484

CAST
(IntegerString AS int)

10

820312

826823

CONVERT
(int,IntegerString)

10

845377

863281

200,000 record dataset
Query Execution_count Avg_worker_time Avg_elapsed_time

PARSE
(IntegerString AS int)

10

20954101

21413086

CAST
(IntegerString AS int)

10

1431640

1508789

CONVERT
(int,IntegerString)

10

1756836

1783203

Looking at these results we can see that the performance is fairly linear when dealing with larger datasets. This is a good thing for us in the sense that we don't have to worry about exponential performance degradation for our tables as they begin to grow.

Next Steps


Last Updated: 2013-08-21


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 17, 2018 - 5:11:31 PM - Pavel Back To Top

Hmm... very interesting results. Why TRY_CAST should differ from TRY_CONVERT so dramatically? It really seems SQL engine recognizes the same plan and does nothing. TRY_PARSE uses .NET FW call so the difference from other two functions is expected but TRY_CAST vs. TRY_CONVERT ?

Also the recommendation "it's best to perform your own testing, in your own environment" is applicable in development but not at the client side obviously. To create special T-SQL code variant for each client is hard to maintain.


Friday, September 26, 2014 - 10:49:04 AM - Jwill Back To Top

We can say that based on the current testing there is always a better method than the new Parse function. The ability to respond with a NULL instead of throw an error can be a big help for validation on large sets of data.


Monday, March 17, 2014 - 10:35:02 AM - Roman Asadovsky Back To Top

Would be interesting to compare performance of the 3 with nvarchar -> int, float and datetime conversion.

Also I would use flushbuffers to compare apples to apples. I suspect initial read influences the numbers more than anything else.


Monday, November 25, 2013 - 9:34:14 AM - Dave Ballantyne Back To Top

As ever the execution plans contain a lot of context here :

The compute scalar operation for both cast and convert ( i only tried the conversion from integerstring to int ) look like this 

 

[Expr1004] = Scalar Operator(CONVERT(int,[tempdb].[dbo].[#TestTable].[IntegerString],0))

ie both are using the internal CONVERT function, there is no cast here ...

 

The PARSE is also interesting, PARSE needs an NVARCHAR so it has to covert from CHAR to NCHAR first.  The execution plan shows this..

[Expr1004] = Scalar Operator(parse(CONVERT_IMPLICIT(nvarchar(128),[tempdb].[dbo].[#TestTable].[IntegerString],0)  AS int))

 


Thursday, September 26, 2013 - 2:23:04 PM - Bhags Back To Top

From the result, It can be analysed that CAST function is perfoming  well compare to  other function for Interger and Float data types conversion, while CONVERT function perfoming well compare to other function for Date datatype.


Thursday, September 26, 2013 - 11:05:55 AM - Ben Snaidero Back To Top

@jmartin:  That is correct in that the first operation on a field is the slowest and this is usually because the data has to be read from disk into memory.   It's because of this I run each function 10 times to mitigate the effects of a first slow call 

Thanks for reading

Ben


Wednesday, September 25, 2013 - 9:56:39 AM - jmarlin Back To Top

Are the results different if you reverse the order so that you don't always do "parse" first? The first operation on the same field of the same table is always worse. Just curious.


Wednesday, August 21, 2013 - 11:05:31 AM - tcstl Back To Top

One note about collasce and convert they can cause the query to ignore indexes


Learn more about SQL Server tools