Lesson Learned #164: Why SQL Server Management Studio runs the query faster than my application

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a service request that our customer reported that a query that they are using is taking more time if they execute the same in SQL Server Management Studio. 

Besides too many things that could impact in the execution of the query, I would like to share another thing that could impact also in the execution.

 

I saw that in many applications, that customer code tries to parametrize the values that it is a good idea, but, you need to review that the parameter type that your application is using. 

 

For example, if your application is running the following query:

 

SET STATISTICS TIME ON SET STATISTICS IO ON DECLARE @Param as nvarchar(200) = 'AnyValue' select * from PerformanceVarcharNVarchar where TextToSearch =@Param

The results as you could see would be:

 

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1633 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

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

Completion time: 2021-02-19T19:39:58.8536169+01:00

 

But, if I run the same execution running the following query:

 

SET STATISTICS TIME ON SET STATISTICS IO ON select * from PerformanceVarcharNVarchar where TextToSearch ='AnyValue'

 

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

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

Completion time: 2021-02-19T19:42:27.2848682+01:00

 

Why? Well, the reason, is that application is using unicode parameter that SQL Server needs to perform an implicit conversion to unicode if the column is not Unicode. In this situation, the column TextToSearch is a varchar and the parameter datatype is unicode. Sometimes, the time is slighty different between executions with/without unicode but if your application executes multiple times having this difference or reading a lot of rows that might cause parallelism in the execution impacting too much in your environment.

 

So, please, remember to use the same parameter datatype that your database has in your application.

 

Enjoy!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.