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.
JyozealAsked:
Who is Participating?
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.

Mike

also see: http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/
0
 
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.
0
 
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.

Mike

-------------
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: http://en.wikipedia.org/wiki/Sargable
0

Experts Exchange Solution brought to you by ConnectWise

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.