Jay-246689 (2/20/2013)
hi,Is CASTing cost an additional overhead to the query? I need to optimize our database initialization and i reviewing some of the sql scripts and found out that there are several sp using this CAST( NULL AS VARBINARY(MAX)) in defining their column. If they cast a null value then it results is always a null value so why they still need to cast it? This sp is called a million times (>7millions calls).
How about a declaring a cursor, what it is overhead cost in terms of performance. They use it in one of their scalar function and being called also several times. this function compares the value of previous record to the current one. the loop will only pass on less than 10 records, and reading to a table containing a millions of records.
any comments, suggestion.
thanks.
The CAST function is pretty negligible, I wouldnt focus on it. The VARBINARY(MAX) has bigger potential issues than the CAST itself. Unless your using FILESTREAM, the page splits can be a bigger cost.
As for CURSORs, they are not evil. Contrary to what the lemmings say. It is what you do IN the cursor loop that is the danger. There are times and places for them, and most of the time they can be replaced with TSQL alternatives. Knowing when they are the correct tool, and being cognizant of what your doing in the loop can make them reasonably efficient.