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

Published Feb 19 2021 10:56 AM 1,681 Views

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!

%3CLINGO-SUB%20id%3D%22lingo-sub-2149992%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23164%3A%20Why%20SQL%20Server%20Management%20Studio%20runs%20the%20query%20faster%20than%20my%20application%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149992%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20reported%20that%20a%20query%20that%20they%20are%20using%20is%20taking%20more%20time%20if%20they%20execute%20the%20same%20in%20SQL%20Server%20Management%20Studio.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBesides%20too%20many%20things%20that%20could%20impact%20in%20the%20execution%20of%20the%20query%2C%20I%20would%20like%20to%20share%20another%20thing%20that%20could%20impact%20also%20in%20the%20execution.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20saw%20that%20in%20many%20applications%2C%20that%20customer%20code%20tries%20to%20parametrize%20the%20values%20that%20it%20is%20a%20good%20idea%2C%20but%2C%20you%20need%20to%20review%20that%20the%20parameter%20type%20that%20your%20application%20is%20using.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20if%20your%20application%20is%20running%20the%20following%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESET%20STATISTICS%20TIME%20ON%0ASET%20STATISTICS%20IO%20ON%0ADECLARE%20%40Param%20as%20nvarchar(200)%20%3D%20'AnyValue'%0Aselect%20*%20from%20PerformanceVarcharNVarchar%20where%20TextToSearch%20%20%3D%40Param%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20results%20as%20you%20could%20see%20would%20be%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%20Execution%20Times%3A%3CBR%20%2F%3ECPU%20time%20%3D%200%20ms%2C%20%3CSTRONG%3Eelapsed%20time%20%3D%201633%20ms.%3C%2FSTRONG%3E%3CBR%20%2F%3ESQL%20Server%20parse%20and%20compile%20time%3A%20%3CBR%20%2F%3ECPU%20time%20%3D%200%20ms%2C%20elapsed%20time%20%3D%200%20ms.%3C%2FP%3E%0A%3CP%3ESQL%20Server%20Execution%20Times%3A%3CBR%20%2F%3ECPU%20time%20%3D%200%20ms%2C%20elapsed%20time%20%3D%200%20ms.%3C%2FP%3E%0A%3CP%3ECompletion%20time%3A%202021-02-19T19%3A39%3A58.8536169%2B01%3A00%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%2C%20if%20I%20run%20the%20same%20execution%20running%20the%20following%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESET%20STATISTICS%20TIME%20ON%0ASET%20STATISTICS%20IO%20ON%0Aselect%20*%20from%20PerformanceVarcharNVarchar%20where%20TextToSearch%20%20%3D'AnyValue'%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%20Execution%20Times%3A%3CBR%20%2F%3ECPU%20time%20%3D%200%20ms%2C%20%3CSTRONG%3Eelapsed%20time%20%3D%200%20ms.%3C%2FSTRONG%3E%3CBR%20%2F%3ESQL%20Server%20parse%20and%20compile%20time%3A%20%3CBR%20%2F%3ECPU%20time%20%3D%200%20ms%2C%20elapsed%20time%20%3D%200%20ms.%3C%2FP%3E%0A%3CP%3ESQL%20Server%20Execution%20Times%3A%3CBR%20%2F%3ECPU%20time%20%3D%200%20ms%2C%20elapsed%20time%20%3D%200%20ms.%3C%2FP%3E%0A%3CP%3ECompletion%20time%3A%202021-02-19T19%3A42%3A27.2848682%2B01%3A00%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhy%3F%20Well%2C%20the%20reason%2C%20is%20that%20application%20is%20using%20unicode%20parameter%20that%20SQL%20Server%20needs%20to%20perform%20an%20implicit%20conversion%20to%20unicode%20if%20the%20column%20is%20not%20Unicode.%20In%20this%20situation%2C%20the%20column%20TextToSearch%20is%20a%20varchar%20and%20the%20parameter%20datatype%20is%20unicode.%20Sometimes%2C%20the%20time%20is%20slighty%20different%20between%20executions%20with%2Fwithout%20unicode%20but%20if%20your%20application%20executes%20multiple%20times%20having%20this%20difference%20or%20reading%20a%20lot%20of%20rows%20that%20might%20cause%20parallelism%20in%20the%20execution%20impacting%20too%20much%20in%20your%20environment.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20please%2C%20remember%20to%20use%20the%20same%20parameter%20datatype%20that%20your%20database%20has%20in%20your%20application.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2149992%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20reported%20that%20a%20query%20that%20they%20are%20using%20is%20taking%20more%20time%20if%20they%20execute%20the%20same%20in%20SQL%20Server%20Management%20Studio.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBesides%20too%20many%20things%20that%20could%20impact%20in%20the%20execution%20of%20the%20query%2C%20I%20would%20like%20to%20share%20another%20thing%20that%20could%20impact%20also%20in%20the%20execution.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Feb 19 2021 10:56 AM
Updated by: