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

by | Feb 19, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

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!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment