x

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 
more ▼

asked Oct 20, 2009 at 10:15 AM in Default

Raj More gravatar image

Raj More
1.7k 79 82 84

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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 ).

more ▼

answered Oct 20, 2009 at 10:41 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

yes i do have an index on Col17
Oct 20, 2009 at 10:44 AM Raj More
Then you will have an index seek or clustered index seek as long as the column is selective enough, but remember that if you get an index seek (index on col17 is not clustered), you would most likely have a key look up also. If you want to prevent the key lookup you need to add col1, col2, col3 in the index (as included column?), or make the index clustered.
Oct 20, 2009 at 10:58 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 20, 2009 at 02:08 PM

Laerte Junior gravatar image

Laerte Junior
488 2

(comments are locked)
10|1200 characters needed characters left

It depends on the datatype of Col17.

more ▼

answered Oct 20, 2009 at 10:39 AM

Peso gravatar image

Peso
1.6k 5 6 8

can you elaborate how the datatype will affect this?
Oct 20, 2009 at 10:45 AM Raj More
If you convert the parameter to VARCHAR but the column is type INT or something else, then you'll get an implicit conversion which could prevent the use of whatever index you have in place.
Oct 20, 2009 at 02:57 PM Grant Fritchey ♦♦
But can you say for sure that you will have an implicit conversion of the column and not the parameter? I tried it with col17 as an int and in my case the parameter was implicit converted to an int, not the other way around. If the data in parameter was character, i got a conversion error.
Oct 21, 2009 at 07:00 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x246

asked: Oct 20, 2009 at 10:15 AM

Seen: 2083 times

Last Updated: Oct 20, 2009 at 10:15 AM