June 30, 2016 at 9:56 am
Hi,
I have SQL Server 2014 SP1 Developer Edition with CU7. I have noticed a significant performance problem with using TRY_PARSE versus TRY_CAST. In a query with around 500,000 rows (ETL job), using TRY_PARSE causes significant CPU utilization and is much slower than TRY_CAST.
What I've observed is that the code that uses TRY_CAST (queries 4 and 5 below) is basically identical to just selecting the DateString field with no transformation applied (query 1). The TRY_PARSE queries (queries 2 and 3) are significantly slower and pegs the CPU. All of these performance metrics are on my laptop with an SSD. I believe that the extra T-SQL code I wrote with the CASE block will cause the same results to occur in queries 4 and 5 (TRY_PARSE) vs 2 and 3 (TRY_CAST) in the presence of bad data.
Am I doing something wrong here or does this seem like a bug? Code to create a sample data set is below. Thanks!
--Create a table to store sample data
CREATE TABLE TryParse_PerformanceBug_DateData (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DateString CHAR(10)
)
--Generate sample date data
DECLARE @RowIndex INT = 0;
WHILE (@RowIndex < 500000)
BEGIN
-- Generate a "random date"
INSERT INTO TryParse_PerformanceBug_DateData (DateString)
VALUES ( CAST(FLOOR(200 * RAND()) + 1900 AS VARCHAR(4)) + '-' +
RIGHT('0' + CAST(FLOOR(12 * RAND()) + 1 AS VARCHAR(2)), 2) + '-' +
RIGHT('0' + CAST(FLOOR(28 * RAND()) + 1 AS VARCHAR(2)), 2)
);
SET @RowIndex += 1;
END
--Query 1 - takes ~5 sec
select ID, DateString FROM TryParse_PerformanceBug_DateData
--Query 2 - takes ~49 sec
SELECT ID, TRY_PARSE(DateString as DATE) as [DateStringAsDate] FROM dbo.TryParse_PerformanceBug_DateData;
--Query 3 - takes ~29 sec, pegs CPU for about 25 sec
SELECT ID, TRY_PARSE(DateString as DATE) as [DateStringAsDate] FROM dbo.TryParse_PerformanceBug_DateData ORDER BY 2;
--Query 4 - takes ~5 sec
SELECT ID, CASE WHEN DateString = '1900-01-01' THEN CAST('1900-01-01' AS DATE)
ELSE NULLIF(TRY_CAST(DateString as DATE), '1900-01-01') END as [DateStringAsDate]
FROM dbo.TryParse_PerformanceBug_DateData;
--Query 5 - takes ~5 sec
SELECT ID, CASE WHEN DateString = '1900-01-01' THEN CAST('1900-01-01' AS DATE)
ELSE NULLIF(TRY_CAST(DateString as DATE), '1900-01-01') END as [DateStringAsDate]
FROM dbo.TryParse_PerformanceBug_DateData
ORDER BY 2;
June 30, 2016 at 10:25 am
TRY_PARSE is a CLR function which could account for the performance difference.
I think the only benefit of using TRY_PARSE over TRY_CAST is when you need to pass the culture that the string is formatted in.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply