Cast and Convert Performance in SQL Server

Cast and Convert Performance in SQL Server

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

Cast Syntax

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

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

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:

Query II :

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.

Leave a Reply