question

Raj More avatar image
Raj More asked

Performance tuning for variables

Is there a performance difference between these, and if so, why?

SELECT Col1, Col2, Col3
FROM dbo.MYTable
WHERE Col17 = CONVERT (VARCHAR (10), @Parameter1)

and

SELECT Col1, Col2, Col3
FROM dbo.MYTable
WHERE CONVERT (VARCHAR (10), @Parameter1) = Col17
t-sqlperformance
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

As long as you only use any function on the parameter, the query plan remains the same. If you alter your statement and use the conversion on the column you will get a different query plan, and SQL will use a table scan instead ( I assume you have an index on col17 ).

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Laerte Junior avatar image
Laerte Junior answered

No, in this case with convert function not at the column table, the result and cost is the same

create table  xxxx (col int)
go
create index aaa on xxxx(col)
go
insert into xxxx values (5) 	
go 10000    
insert into xxxx values (3) 	
go 10000    			
insert into xxxx values (1) 	
go 10000    

declare @Parameter1 int
set @Parameter1 = 5

SELECT * FROM xxxx WHERE Col = CONVERT (VARCHAR (10), @Parameter1)

**SELECT * FROM xxxx WHERE Col = CONVERT (VARCHAR (10), @Parameter1)
  |--Index Seek(OBJECT:([DBA].[dbo].[xxxx].[aaa]), SEEK:([DBA].[dbo].[xxxx].
[col]=CONVERT_IMPLICIT(int,CONVERT(varchar(10),[@Parameter1],0),0)) ORDERED FORWARD)**  

SELECT * FROM xxxx WHERE CONVERT (VARCHAR (10), @Parameter1) = Col  	

**SELECT * FROM xxxx WHERE CONVERT (VARCHAR (10), @Parameter1) = Col
  |--Index Seek(OBJECT:([DBA].[dbo].[xxxx].[aaa]), SEEK:([DBA].[dbo].[xxxx].
[col]=CONVERT_IMPLICIT(int,CONVERT(varchar(10),[@Parameter1],0),0)) ORDERED FORWARD)**

Just to complement, you can optimize the statment with covered index, but in this case both in 1 case and in the second the index will be used in the same way and cost (includin IO statisics). And you can change the datatype in convert function, because the optimizer perfectly convert to correct dataype using CONVERT_IMPLICT. The correct is not that, its you use the same dataype and avoid convert_implicit. But if you really need use convert, in this case, with this 2 statments there is no difference because the convert function is not in the table column. If it were, will probably be a table scan or clustered index scan if the table had a clustered index

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

It depends on the datatype of Col17.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.