Cast and Convert Performance in SQL Server

Yusuf KAHVECİ

5 years ago

Conversion operations in datetime data types often cause poor performance but they are always the ones that are overlooked. We will examine the Cast and Convert Performance in SQL Server in this article.

Yes, they both do the same job, but as a result of our example, there can be big differences between them according to the query design and the scenario where it is used.

Convert Syntax

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

1	CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Cast Syntax

CAST ( expression AS data_type [ ( length ) ] )

1	CAST ( expression AS data_type [ ( length ) ] )

Now let’s create a sample table and insert data into that table. (You can perform this process with Query below.)

CREATE TABLE DatePerformance
    (
      Id INT IDENTITY
             PRIMARY KEY ,
      Descriptions CHAR(500) DEFAULT 'Yusuf Kahveci' ,
      DateColumn DATETIME,
    );


INSERT  INTO DatePerformance
        ( DateColumn
        )
        SELECT  DATEADD(MI, RAND() * 10000, GETDATE())
GO 100000

CREATE TABLE DatePerformance

(

Id INT IDENTITY

PRIMARY KEY ,

Descriptions CHAR(500) DEFAULT 'Yusuf Kahveci' ,

DateColumn DATETIME,

);

INSERT INTO DatePerformance

( DateColumn

)

SELECT DATEADD(MI, RAND() * 10000, GETDATE())

GO 100000

After completing the above process, let’s create the following Index on the DateColumn column in our table we have created.

CREATE INDEX SQLNCIX_DataPerformance_DateColumn 
ON DatePerformance 
(
DateColumn);

CREATE INDEX SQLNCIX_DataPerformance_DateColumn

ON DatePerformance

(

DateColumn);

Now we have an environment where we can perform our demo. Now we need to write two separate Queries using the Convert function and the Cast function and examine their values.

Query I:

-- SESSION I
SET STATISTICS IO ON;

DECLARE @SqlTr_Date DATETIME = '2016-07-15 16:38:49.490';

SELECT  DateColumn
FROM    DatePerformance
WHERE   CONVERT(VARCHAR, DateColumn, 112) = CONVERT(VARCHAR, @SqlTr_Date, 112);

SET STATISTICS IO OFF;

-- SESSION I

SET STATISTICS IO ON;

DECLARE @SqlTr_Date DATETIME = '2016-07-15 16:38:49.490';

SELECT DateColumn

FROM DatePerformance

WHERE CONVERT(VARCHAR, DateColumn, 112) = CONVERT(VARCHAR, @SqlTr_Date, 112);

SET STATISTICS IO OFF;

Query II :

SET STATISTICS IO ON;

DECLARE @SqlTr_Date DATETIME = '2016-07-15 16:38:49.490';
SELECT  DateColumn
FROM    DatePerformance
WHERE   CAST(DateColumn AS DATE) = CAST(@SqlTr_Date AS DATE);

SET STATISTICS IO OFF;

SET STATISTICS IO ON;

DECLARE @SqlTr_Date DATETIME = '2016-07-15 16:38:49.490';

SELECT DateColumn

FROM DatePerformance

WHERE CAST(DateColumn AS DATE) = CAST(@SqlTr_Date AS DATE);

SET STATISTICS IO OFF;

When we examine the above Queries, we see the expression “SET STATISTICS IO ON”. With this expression we get the IO values of our Query. Thus, we will be able to evaluate the Performance by comparing the IO values of the two Query.

Also press the “Actual Execution Plan” button before pressing the Execute button. Thus, it will prepare the Execution Plan and we can compare Execution Plans.

You can see the statistical value and Execution Plan that created when we Execute Query I.

Q I – Statistics:

(14392 row(s) affected)

Table ‘DatePerformance’. Scan count 1, logical reads 825, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Q I – Execution Plan :

Q II – Statistics:

(14392 row(s) affected)

Table ‘DatePerformance’. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Q II – Execution Plan :

As we have seen, we have written two separate queries with the CONVERT and CAST functions and found that there is a serious IO difference and Execution Plan difference in performance evaluation.

We have seen in our article that we need to be careful in our processes such as date formatting. We have also realized that it is very important that we use functions by analyzing Query design.

Good luck with.