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

avatar image

Raj More
1.8k 82 87 90

(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

avatar image

Håkan Winther
16.5k 36 45 57

(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

avatar image

Laerte Junior
488 2 4

(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

avatar image

Peso
1.6k 5 6 9

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

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:

x1067
x290

asked: Oct 20, 2009 at 10:15 AM

Seen: 2400 times

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

Copyright 2016 Redgate Software. Privacy Policy