Does Cast() and Convert() make query performance low??

Question

In my query i have cast and convert functions,which made my query run for long time. When i removed those functions and replaced them with day/month/year functions and concatenated them for getting a format of date which took less time. Is this because convert() or cast() takes more execution time ? Thank you in advance

Vikrant More · Answer

It depends, using in-build / user defined function in the where condition or in the on clause of joins will degrade the performance of query. if you are doing cast/convert one time and using it in where condition then separate it out from where condition and keeping it in local variable and replacing local variable at where condition instead of function. this is applicable to scalar functions. using scalar functions in where condition can cause to run function with every row to check for value we are passing lets say if there are 100 rows then the function will run with every 100 rows. refer below links for more understanding of it, http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/ http://blogs.msdn.com/b/sqlserverfaq/archive/2009/10/06/performance-benefits-of-using-expression-over-user-defined-functions.aspx

Manish Kumar Choudhary · Answer

yes definitely cast () and convert () will effect the performance becouse this is extra work sql server compiler has to do whkle creating the result set for the query so some how it will effect the time of exicution of the query.

Vulpes · Answer

Well, clearly the use of the CAST or CONVERT functions will have some impact on query performance because of the time needed to make the convertion. How much impact they have will depend on the nature and number of conversions to be made though I'd have thought that Date/Time conversions will be at the higher end of the scale. So, I'm not surprised if using a different approach in your case has improved performance. Of course, you get similar discrepancies when using 'library functions' from any programming language (not just domain-specific ones such as SQL). Sometimes it's much more performant to do things in a certain way.

Mukesh Kumar Tiwari · Answer

http://searchsqlserver.techtarget.com/tip/The-difference-between-CONVERT-and-CAST-in-SQL-Server