cast - performance

Hi All,

As part of implementing search functionality i am using CAST in sql server stored procedure. the Sp is running very slow.  Will this cause performance issues? if yes what other alternatives are available?

Thanks for your time.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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.


also see:
JyozealAuthor Commented:
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

Open 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.
Mike EghtebasDatabase and Application DeveloperCommented:
So long as a no field is used in a function will do. Can you apply this concept to your query and test it?

You need to post your entire WHERE clause to see what exactly you have.


FYI: A query like ... Where cast(integercolumn as varchar(100)) =@inputsearchterm; is known as none sargable. On the other hand, a query like: ... Where integercolumn = cast(@inputsearch as varchar(100)) ; is known as sargable.

Where cast(integercolumn as varchar(100)) =@inputsearchterm;         <-- none sargable
Where integercolumn = cast(@inputsearch as varchar(100));                <-- sargable

Where integercolumn = @inputsearch;                <-- also sargable

sargable: means that a query is capable of having the query engine optimize the execution plan that the query uses.

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.  See:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.