cast - performance

You are possibly applying cast to a field in the table in where clause like

Where Cast(Field1 as varchar(10)) ='xyz'

This means if you have 1000 rows, it executes case() 1000 times once for each row.

The optimal performance will be possible if you could use something like:

Where Field1 =Cast('xyz' as varchar(10))   <-- here cast is executed only once.

It will help if you include a sample code so we don't have to do these kinds of guess work.

Mike

also see: http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

eghtebas, thanks for your comments. you are right that i am using the cast the way you guessed.As part of search functionality, i am searching the inputsearch term for match against the values in 8 columns of temp table. right now the query looks like

Select * from #temp
where cast(integercolumn as varchar(100)) = @inputsearchterm
or varcharcolumn  like ('%' + @inputsearchterm + '%')
or cast(integercolumn2 as varchar(100)) = @inputsearchterm
..
.
.
till 8 columns

Select allOpen in new window

I have around 180000 rows loaded in temp.So this is running very slow. 3 columns are numeric. 3 are alphabetic. 2 columns are alphanumeric. i am now trying to check if input term is numeric then only check against numeric columns and if the input term is alphabetic then only check for columns that are only alphabetic. Do you think this works or you suggest any better approach?

Thanks for your time.