Query performance and plan cache issues when parameter length not specified correctly

re: Query performance and plan cache issues when parameter length not specified correctly

Milind Trivedi — Sun, 01 Jan 2012 20:23:02 GMT

Hi,

Just want to confirm from you that, if table column length has varchar(50) and query variable length has varchar(25) does this difference make any difference in the query execution?

Example:

--==============

Create table TableName (Col1 int, Col2 varchar(50))

declare @str varchar(25)

select @str = 'my text'

select * from TableName where Col2 = @str

--==============

As per execution plan there is no difference. Can you please confirm? Is that SQL internally adjusting the length or it will ignore because of length is less than actual length?

Thanks..

re: Query performance and plan cache issues when parameter length not specified correctly

JackLi — Wed, 06 Oct 2010 13:30:11 GMT

Regarding Michael's comment about nvarchar and varchar, it's a typo. It's corrected now. thanks for pointing this out. The example intended to use varchar data type though the problem also occurs for nvarchar data type. If your column is varchar, you will need to use SqlDbType.VarChar. If your column is nvarchar, you will need to use SqlDbType.NVarChar.

Regarding JR's question: None of the two problems described here apply to stored procedure. Stored procedure will perform just fine and there will be not cache bloating either.

re: Query performance and plan cache issues when parameter length not specified correctly

Michael Codanti — Wed, 06 Oct 2010 05:47:30 GMT

I noticed that in addition to specifying the size you changed the type from NVARCHAR to VARCHAR:

Orig: cmd.Parameters.Add("@c2", SqlDbType.NVarChar).Value = str;

New: cmd.Parameters.Add("@c2", SqlDbType.VarChar, 500).Value = str;

Would that have anything to do with the linked query performance issue?

re: Query performance and plan cache issues when parameter length not specified correctly

JR — Tue, 05 Oct 2010 21:27:30 GMT

Does this affect stored procedure execution where CommandType is StoredProcedure?